read/write BLOBS in sqlser using asp.net

here i have some problem related to BLOBS in sqlserver
i need to store .bmp or .jpg files in sqlserver2000 and using
asp.net i need to retrive that images and show in a webform.
if user want to insert a new Image into database i need to do
it from frontend using asp.net
just like i need add,update,edit images using asp.net
can any one give me idea how to approch.

thanks in advance..
-SridharHere's a KnowledgeBase article that explains it:HOW TO: Read and Write a File to and from a BLOB Column by Using Chunking in ADO.NET and Visual Basic .NET. It includes links to equivalent versions of the article for C++ and C#.

Don

Read Write Image data with Sqlceserver

I was wondering if it is possible to read image data from a database in .net compact framework. Since cf does not have image.fromstream(memstream) to work with, I don't know how else to read the image from the database and then place it into a picturebox.

Here is the code I have been trying out:

Dim Img As Image
'
Dim conn As New SqlCeConnection("Data Source = \My Documents\test2.sdf")

conn.Open()

Dim sql As String = "SELECT * FROM Dater"
Dim cmd As New SqlCeCommand(sql, conn)
Dim reader As SqlCeDataReader = _
cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

While reader.Read()

TextBox1.Text = reader.Item("name")
Dim b(reader.GetBytes(1, 0, Nothing, 0, Integer.MaxValue) - 1) As Byte

reader.GetBytes(1, 0, b, 0, b.Length)
Dim ms As New System.IO.MemoryStream(b)
Dim bmp As New Bitmap(ms) <-Error: Value does not fall within expected range
Img = bmp

End While

PictureBox2.Image = Img

I get an error ,Value does not fall within expected range.

Does this mean the image was not save correctly in the database?

Thanks for any helpJust to give somebody an idea on how to do this you can try converting an image to a string and back again using the convertFromBase64 and convertToBase64 functions. Then you can save an image to a database as a string using SQLServer.

Thanks for all of your help, really appreciate it.

sql

Read Write Image data with Sqlceserver

I was wondering if it is possible to read image data from a database in .net compact framework. Since cf does not have image.fromstream(memstream) to work with, I don't know how else to read the image from the database and then place it into a picturebox.

Here is the code I have been trying out:

Dim Img As Image
'
Dim conn As New SqlCeConnection("Data Source = \My Documents\test2.sdf")

conn.Open()

Dim sql As String = "SELECT * FROM Dater"
Dim cmd As New SqlCeCommand(sql, conn)
Dim reader As SqlCeDataReader = _
cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

While reader.Read()

TextBox1.Text = reader.Item("name")
Dim b(reader.GetBytes(1, 0, Nothing, 0, Integer.MaxValue) - 1) As Byte

reader.GetBytes(1, 0, b, 0, b.Length)
Dim ms As New System.IO.MemoryStream(b)
Dim bmp As New Bitmap(ms) <-Error: Value does not fall within expected range
Img = bmp

End While

PictureBox2.Image = Img

I get an error ,Value does not fall within expected range.

Does this mean the image was not save correctly in the database?

Thanks for any helpJust to give somebody an idea on how to do this you can try converting an image to a string and back again using the convertFromBase64 and convertToBase64 functions. Then you can save an image to a database as a string using SQLServer.

Thanks for all of your help, really appreciate it.

Read SQL Profiler Trace output

Hi Folks,

I want to Read the SQL Profiler Trace Output using VB or VB.NET

I read some forums. They advice to import SMO in VB.NET..

But how can i Import this....

This is very Urgent for me....

Please Reply me immediately...You don't need SMO to read trace output. select from ::fn_get_trace into a table, then use normal ADO.Net to access the trace data from the table.|||Thanks for Reply...

Please can u give me a sample code for this....

Please this is very urgent for me...|||

First thing you need to do is set up your trace to go into a file. (Sending the trace data directly into SQL Server is very bad for performance.) Once you've got the trace file you can do something like this (which is right out of Books Online - see that document for more details.)

USE AdventureWorks;

GO

SELECT * INTO temp_trc

FROM fn_trace_gettable('c:\temp\my_trace.trc', default);

GO|||Thanks for help me...

Read DESCRIPTION from a backuped DB file in .Net

Hi,
How can I read the "Description" field from a backuped DB (file) in .Net?
Some ideas?
Thanks,
George.What version are you using?
Where does the "Description"column exist?
If you are using SQL Server 2005 see sys.database_files table
There is state_desc column.
"George Homorozeanu" <george_homorozeanu@.hotmail.com> wrote in message
news:u9E2vXRQGHA.740@.TK2MSFTNGP12.phx.gbl...
> Hi,
> How can I read the "Description" field from a backuped DB (file) in .Net?
> Some ideas?
> Thanks,
> George.
>|||I am using SQL Server 2000 and I want to be able to read the descriprion
text entered for the DESCRIPTION option when backing up a database.
Example:
BACKUP DATABASE atsTables
TO DISK = 'c:\temp\20060306_1245_atsTables.dat'
WITH
DESCRIPTION = 'atsTables Backup1'
I want to build a simple application where the user can select a file
(backedup db file) and the application shows in a textbox the description
text ('atsTables Backup1' from the above example). Is this possible?
Thanks,
George.
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:%23buDpeRQGHA.1096@.TK2MSFTNGP11.phx.gbl...
> What version are you using?
> Where does the "Description"column exist?
> If you are using SQL Server 2005 see sys.database_files table
> There is state_desc column.
>
>
>
> "George Homorozeanu" <george_homorozeanu@.hotmail.com> wrote in message
> news:u9E2vXRQGHA.740@.TK2MSFTNGP12.phx.gbl...
>|||Lookup backupmediaset system table in the BOL
"George Homorozeanu" <george_homorozeanu@.hotmail.com> wrote in message
news:OGFY%23lRQGHA.1688@.TK2MSFTNGP11.phx.gbl...
>I am using SQL Server 2000 and I want to be able to read the descriprion
>text entered for the DESCRIPTION option when backing up a database.
> Example:
> BACKUP DATABASE atsTables
> TO DISK = 'c:\temp\20060306_1245_atsTables.dat'
> WITH
> DESCRIPTION = 'atsTables Backup1'
>
> I want to build a simple application where the user can select a file
> (backedup db file) and the application shows in a textbox the description
> text ('atsTables Backup1' from the above example). Is this possible?
> Thanks,
> George.
> "Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
> news:%23buDpeRQGHA.1096@.TK2MSFTNGP11.phx.gbl...
>|||You mean the information returned from RESTORE HEADERONLY? Sure, just execut
e that SQL command and
treat the result as any result from a SELECT statement.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"George Homorozeanu" <george_homorozeanu@.hotmail.com> wrote in message
news:u9E2vXRQGHA.740@.TK2MSFTNGP12.phx.gbl...
> Hi,
> How can I read the "Description" field from a backuped DB (file) in .Net?
Some ideas?
> Thanks,
> George.
>|||Yes Tibor that's what I needed.
Thanks for help guys
George.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb
im Newsbeitrag news:eO$ckrRQGHA.3984@.TK2MSFTNGP14.phx.gbl...
> You mean the information returned from RESTORE HEADERONLY? Sure, just
> execute that SQL command and treat the result as any result from a SELECT
> statement.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "George Homorozeanu" <george_homorozeanu@.hotmail.com> wrote in message
> news:u9E2vXRQGHA.740@.TK2MSFTNGP12.phx.gbl...
>sql

Read and write a Constraint or Default Value

Okay, maybe I'm getting ahead of myself.

Using SQL Server Express, VWD and .net 2.0 I've figured out how to drop a Table Column Constraint or Default Value/Binding and then Create it again using a stored procedure. What I can't figure out is how to retrieve that column's constraint value and write it to, say a label, in an aspx page, simply for reference. Is it possible? In this case the Data Type of the column is money.

I'm using it to perform a calculation to a column with a value that the user inserts into another column. (Column1(user input) minus Column2(with Default Value) = Column3(Difference). I just want to read Column2's Default Value for reference so I know whether to change it or not.

Tables have Check Constraints, Columns do not.

Open up the Master database and take a look inside.

You are looking for the INFORMATION_SCHEMA views.

In particular, you want this one: INFORMATION_SCHEMA.CHECK_CONSTRAINTS and INFORMATION_SCHEMA.COLUMNS

|||

If I open dbo.tbl1 in my database and right click to modify, I can put a Default Value or Binding of 1000000.00(or whatever) in a column I've named "Gen_ourlim". When I do that, there is, in the folder "Constraints" under dbo.tbl1, something created called DF_tbl1_Gen_ourlim. When I drop that Default Value from column "Gen_ourlim" the DF_tbl1_Gen_ourlim goes away in the Constraint folder. That's why I called the column having a constraint I suppose. In any case, how can I read that Default Value and write it to a web page?

|||

Did you query INFORMATION_SCHEMA.COLUMNS?

The default value is a column in that view, as is the catalog name, the schema name, the table name and the column name. Plus lots of other goodies about a column.

Querying data and placing it on a web page is an entirely different discussion and belongs in one of the web forums.

|||

Thanks David,

Got it. At least I was able to query it:

USE <path to database>

SELECT Column_Name, Column_Default

frominformation_schema.columnswhere table_name='tbl1'and Column_name='Gen_ourlim'

|||

Then you need to mark this thread as resolved, mark answers as appropriate, and start a new thread in a nore appropriate forum on how to put data on a web page.

(But the internet is chock full of how-to articles on that, and any beginner asp.net book will tell you how also. Best to start doing it and ask specific questions when you get stuck.)

|||

Will do David. Thanks again. Using the query in VWD is the easy part. Put the Select in a stored procedure in your Database Explorer as such:

ALTER PROCEDURE dbo.GetDefaultValue

AS
SELECT COLUMN_NAME, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (TABLE_NAME ='tbl1') AND (COLUMN_NAME = 'Gen_ourlim')
RETURN

Put a SqlDataSource on your page along with a DataView and then configure it to use the stored procedure as the Select in the SqlDataSource. There's a nice little video tutorial on doing this at

http://www.asp.net/learn/sql-videos/video-114.aspx

sql

Read a Database Datetime (datatype) value and pass it into a Parameter

I'm running the following stored proceudre that I will eventually be using checboxes and a sqlDataAdapter to fill a DataGrid using VB.Net.

When I attempt to execute the SP on the server side for testing, it throws me the error "syntax error converting datetime from character string".

conversion of datatypes is something I'm still new to so I can't begin to understand how to write the code thus why I'm seeking help. Here's the SP Code:

As soon as it hits the @.CREATED as datetime =.... this is where it throws that error. Any idea on how to convert the datetime data type to a character string?

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTERPROCEDURE [dbo].[uspPvtSelectCommand]

@.MAC as varchar(18)='00:AC:12:E5:76:9C',

@.CREATED asdatetime='4/25/2007 8:40:50 AM',

@.MODIFIED asdatetime='5/19/2007 5:05:04 AM',

@.WORKSTATION_NAME as varchar(13)='B000E7FF53C72',

@.IP_ADDRESS as varchar(15)='171.136.201.142',

@.USER_NAME as varchar(8)='nbe5533',

@.OPERATING_SYSTEM as varchar(25)='Windows XP Professional',

@.SERVICE_PACK as varchar(3)='2.0',

@.BAND_VERSION as varchar(7)='5.06 b2',

@.WORKSTATION_OU as varchar(200)='CN=B001321D14A41,OU=Desktops,OU=Agents,OU=Card,OU=Customer Service and Support,OU=Utility,OU=NCG,OU=Workstations,OU=BAND,DC=corp,DC=bankofamerica,DC=com',

@.WORKSTATION_OWNER as varchar(200)='CN=Davis\, Wally NBE5533,OU=Distributed Server Support,OU=NCG Administrators,OU=Accounts,OU=BAND,DC=corp,DC=bankofamerica,DC=com',

@.MANUFACTURER as varchar(26)='Dell Computer Corporation;',

@.MODEL as varchar(40)='Latitude D600;',

@.CHASSIS as varchar(10)='8;12;',

@.SERIAL_NUMBER as varchar(30)='.83SFB51.CN486434737027.;',

@.PROCESSOR as varchar(100)='Intel(R) Pentium(R) M processor 1600MHz;',

@.HARD_DRIVE as varchar(40)='FUJITSU MHV2040AH;',

@.HARD_DRIVE_SIZE as varchar(30)='40007761920;',

@.MEMORY as varchar(22)='1073741824;1073741824;',

@.NAME as varchar(100)='KB887979',

@.VERSION as varchar(20)='VALUE DOES NOT EXIST',

@.BUILD as varchar(45)='1.3',

@.INSTALL_STATUS as varchar(20)='1',

@.INSTALL_DATE as varchar(21)='3/31/2005 2:40:34 PM',

@.PACKAGE_NAME as varchar(90)='CRYSTAL_REPORTS_ACTIVEX_VIEWER_10.0_9.2_8.6_8.5_20.05.08.01_WKS_XP2KNT_BAND_I1^EDE'

AS

-- SET NOCOUNT ON;

SELECT main.MAC, main.CREATED, main.MODIFIED, hardware.MANUFACTURER, hardware.MODEL, hardware.CHASSIS, hardware.SERIAL_NUMBER, hardware.PROCESSOR,

hardware.HARD_DRIVE, hardware.HARD_DRIVE_SIZE, hardware.MEMORY, network.WORKSTATION_NAME, network.IP_ADDRESS,

network.USER_NAME, network.OPERATING_SYSTEM, network.SERVICE_PACK, network.BAND_VERSION, network.WORKSTATION_OU,

network.WORKSTATION_OWNER, software.MAC, software.NAME, software.VERSION, software.BUILD, software.INSTALL_STATUS,

software.INSTALL_DATE, software.PACKAGE_NAME

FROM main INNERJOIN

hardware ON main.MAC = hardware.MAC INNERJOIN

network ON main.MAC = network.MAC INNERJOIN

software ON main.MAC = software.MAC

WHERE MAIN.MAC LIKE'%'+@.MAC+'%'AND MAIN.CREATED LIKE'%'+@.CREATED+'%'AND MAIN.MODIFIED LIKE'%'+@.MODIFIED+'%'AND NETWORK.WORKSTATION_NAME LIKE'%'+@.WORKSTATION_NAME+'%'AND NETWORK.IP_ADDRESS LIKE'%'+@.IP_ADDRESS+'%'AND NETWORK.USER_NAMELIKE'%'+@.USER_NAME+'%'AND NETWORK.OPERATING_SYSTEM LIKE'%'+@.OPERATING_SYSTEM+'%'AND NETWORK.SERVICE_PACK LIKE'%'+@.SERVICE_PACK+'%'AND NETWORK.BAND_VERSION LIKE'%'+@.BAND_VERSION+'%'AND NETWORK.WORKSTATION_OU LIKE'%'+@.WORKSTATION_OU+'%'AND NETWORK.WORKSTATION_OWNER LIKE'%'+@.WORKSTATION_OWNER+'%'AND HARDWARE.MANUFACTURER LIKE'%'+@.MANUFACTURER+'%'AND HARDWARE.MODEL LIKE'%'+@.MODEL+'%'AND HARDWARE.CHASSIS LIKE'%'+@.CHASSIS+'%'AND HARDWARE.SERIAL_NUMBER LIKE'%'+@.SERIAL_NUMBER+'%'AND HARDWARE.PROCESSOR LIKE'%'+@.PROCESSOR+'%'AND HARDWARE.HARD_DRIVE LIKE'%'+@.HARD_DRIVE+'%'AND HARDWARE.HARD_DRIVE_SIZE LIKE'%'+@.HARD_DRIVE_SIZE+'%'AND HARDWARE.MEMORY LIKE'%'+@.MEMORY+'%'AND SOFTWARE.NAME LIKE'%'+@.NAME+'%'AND SOFTWARE.VERSION LIKE'%'+@.VERSION+'%'AND SOFTWARE.BUILD LIKE'%'+@.BUILD+'%'AND SOFTWARE.INSTALL_STATUS LIKE'%'+@.INSTALL_STATUS+'%'AND SOFTWARE.INSTALL_DATE LIKE'%'+@.INSTALL_DATE+'%'AND SOFTWARE.PACKAGE_NAME LIKE'%'+@.PACKAGE_NAME+'%'

Thank you,

Wallace

hi, this is because you were concatenating a datetime variable with a string '%'

change this lines on your where clause

--AND MAIN.CREATED LIKE '%'+@.CREATED+'%'
AND MAIN.CREATED = @.CREATED
--AND MAIN.MODIFIED LIKE '%'+@.MODIFIED+'%'
AND MAIN.MODIFIED = @.MODIFIED|||

CREATED LIKE '%'+@.CREATED+'%'

Wallace,

I guess that I am at a loss about why you are concatenating wildcards to a datetime value.

Is there a particular problem you are attempting to solve by so doing?

Without converstion, you cannot add characters ( '%' ) to a datetime datatype. (What exactly do you hope to accomplish by adding '%' to the datetime?)

I am assuming that Main.Created is a datetime datetype.

In fact, I wonder if any of the above parameters really need to have '%' added to each side of the value...

|||

As soon as it hits the @.CREATED as datetime =.... this is where it throws that error. Any idea on how to convert the datetime data type to a character string?

Are you doing this like to compare parts of dates? Like:

drop table dateRow
go
create table dateRow
(
dateValue datetime
)
insert into dateRow
select '20070101'
union all
select '20070201'
union all
select '20070301'
union all
select '20070401'
union all
select '20070501'
union all
select '20070601'
go
--find rows from 2007
select *
from dateRow
where convert(varchar(8),datevalue,112) like '2007_'
go
--find rows from January
select *
from dateRow
where convert(varchar(8),datevalue,112) like '_01__'

--find rows from June
select *
from dateRow
where convert(varchar(8),datevalue,112) like '_06__'

Interesting idea...Probably not perfect in terms of performance. A better way to do this involves having a table of dates that you can join to your date value (if you don't have time values.) Then you can index the month, year, or day values for fast searching.

Here is an article with technique to load the date table: http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!1349.entry

|||

This doesn't cause any errors on my server...

Code Snippet

CREATE PROCEDURE dbo.uspPvtSelectCommand
( @.CREATED as datetime = '4/25/2007 8:40:50 AM',
@.MODIFIED as datetime = '5/19/2007 5:05:04 AM'
)
AS
SELECT getdate(), @.Created, @.Modified
GO

I think the error comes from the concatenation of the datetime parameter as I indicated earlier.

LIKE '%'+@.CREATED+'%' AND MAIN.MODIFIED LIKE '%'+@.MODIFIED+'%'

The following fails with such an error...

Code Snippet

ALTER PROCEDURE dbo.uspPvtSelectCommand
( @.CREATED as datetime = '4/25/2007 8:40:50 AM',
@.MODIFIED as datetime = '5/19/2007 5:05:04 AM'
)
AS
SELECT getdate(), @.Created, ( '%' +@.Modified + '%' )
GO

EXECUTE dbo.uspPvtSelectCommand

Server: Msg 241, Level 16, State 1, Procedure uspPvtSelectCommand, Line 6
Conversion failed when converting datetime from character string.

|||

Hi Arnie,

I figured I would give everyone who's been so kind to help with a little more information. I have a DataGrid in my vb.net app.

I have 25 fields/columns and when I go to click on "Preview" from the DataGrid, it hit's the second field parameter "CREATED" and throws the error, " Enter a value for parameter "CREATED". What I have are a bunch of checkboxes on my form, so that our Managers can click on any combination of checkboxs (that represents a field in one of 4 tables), it will pull up only those checkboxex (fields) of that data, use the SP to join those fields and then store it in the datagrid and then a separate sub-routine that exports it to Excel. So, right now, when I go back to recreate a new SQLDataAdapter in vb.net, when I select the stored procedure, it doesn't see the list of parameters, but, it is connected to the right db server so, the problem seems to be the way my Stored procedure is written.

I have since removed the concatenation and changed it so that MAIN.CREATED = @.CREATED AND MAIN.MODIFIED = @.MODIFIED but still the error.

Any further assistance would be appreciated. Here is what the SP looks like now.

USE platform_validation_tool

GO

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTERPROCEDURE [dbo].[uspPvtSelectCommand]

@.MAC as varchar(18),

@.CREATED asdatetime,

@.MODIFIED asdatetime,

@.WORKSTATION_NAME as varchar(13),

@.IP_ADDRESS as varchar(15),

@.USER_NAME as varchar(8),

@.OPERATING_SYSTEM as varchar(25),

@.SERVICE_PACK as varchar(3),

@.BAND_VERSION as varchar(7),

@.WORKSTATION_OU as varchar(200),

@.WORKSTATION_OWNER as varchar(200),

@.MANUFACTURER as varchar(26),

@.MODEL as varchar(40),

@.CHASSIS as varchar(10),

@.SERIAL_NUMBER as varchar(30),

@.PROCESSOR as varchar(100),

@.HARD_DRIVE as varchar(40),

@.HARD_DRIVE_SIZE as varchar(30),

@.MEMORY as varchar(22),

@.NAME as varchar(100),

@.VERSION as varchar(20),

@.BUILD as varchar(45),

@.INSTALL_STATUS as varchar(20),

@.INSTALL_DATE as varchar(21),

@.PACKAGE_NAME as varchar(90)

AS

-- SET NOCOUNT ON;

SELECT main.MAC,getdate(), @.CREATED, @.MODIFIED, hardware.MANUFACTURER, hardware.MODEL, hardware.CHASSIS, hardware.SERIAL_NUMBER, hardware.PROCESSOR,

hardware.HARD_DRIVE, hardware.HARD_DRIVE_SIZE, hardware.MEMORY, network.WORKSTATION_NAME, network.IP_ADDRESS,

network.USER_NAME, network.OPERATING_SYSTEM, network.SERVICE_PACK, network.BAND_VERSION, network.WORKSTATION_OU,

network.WORKSTATION_OWNER, software.MAC, software.NAME, software.VERSION, software.BUILD, software.INSTALL_STATUS,

software.INSTALL_DATE, software.PACKAGE_NAME

FROM main INNERJOIN

hardware ON main.MAC = hardware.MAC INNERJOIN

network ON main.MAC = network.MAC INNERJOIN

software ON main.MAC = software.MAC

WHERE MAIN.MAC LIKE'%'+@.MAC+'%'AND MAIN.CREATED = @.CREATED AND MAIN.MODIFIED = @.MODIFIED AND NETWORK.WORKSTATION_NAME LIKE'%'+@.WORKSTATION_NAME+'%'AND NETWORK.IP_ADDRESS LIKE'%'+@.IP_ADDRESS+'%'AND NETWORK.USER_NAMELIKE'%'+@.USER_NAME+'%'AND NETWORK.OPERATING_SYSTEM LIKE'%'+@.OPERATING_SYSTEM+'%'AND NETWORK.SERVICE_PACK LIKE'%'+@.SERVICE_PACK+'%'AND NETWORK.BAND_VERSION LIKE'%'+@.BAND_VERSION+'%'AND NETWORK.WORKSTATION_OU LIKE'%'+@.WORKSTATION_OU+'%'AND NETWORK.WORKSTATION_OWNER LIKE'%'+@.WORKSTATION_OWNER+'%'AND HARDWARE.MANUFACTURER LIKE'%'+@.MANUFACTURER+'%'AND HARDWARE.MODEL LIKE'%'+@.MODEL+'%'AND HARDWARE.CHASSIS LIKE'%'+@.CHASSIS+'%'AND HARDWARE.SERIAL_NUMBER LIKE'%'+@.SERIAL_NUMBER+'%'AND HARDWARE.PROCESSOR LIKE'%'+@.PROCESSOR+'%'AND HARDWARE.HARD_DRIVE LIKE'%'+@.HARD_DRIVE+'%'AND HARDWARE.HARD_DRIVE_SIZE LIKE'%'+@.HARD_DRIVE_SIZE+'%'AND HARDWARE.MEMORY LIKE'%'+@.MEMORY+'%'AND SOFTWARE.NAME LIKE'%'+@.NAME+'%'AND SOFTWARE.VERSION LIKE'%'+@.VERSION+'%'AND SOFTWARE.BUILD LIKE'%'+@.BUILD+'%'AND SOFTWARE.INSTALL_STATUS LIKE'%'+@.INSTALL_STATUS+'%'AND SOFTWARE.INSTALL_DATE LIKE'%'+@.INSTALL_DATE+'%'AND SOFTWARE.PACKAGE_NAME LIKE'%'+@.PACKAGE_NAME+'%'

Thanks and Sincerely,

Wallace

|||

Wallace,

I need you to clarify.

If I understand this correctly, you are passing into the stored procedure the checkbox values for each of the parameters listed. Is that correct?

And what checkboxes are checked determines what data is expected?

OR,

Is there actual values in the input parameters, AND the presence of a check indicated to use that value in the WHERE clause...

|||That's correct, I want to pass values, being read from the database, into the sql sp parameters, based on which checkboxes are checked.

The values will be read from the Database, passed into the parameter, and joined via the SP into a DataGrid. I have a separate vb.net sub routine that will take all of this and export to excel.

Thanks,

Wally

|||

I'm trying to understand and help, but I'm still confused. You replied "That's correct..." to two diametrically opposed questions.

Do you wish to collect multiple rows of data from the database and display that data in the DataGrid?

How do you determine what should be displayed?

Are all columns always returned and displayed?

Is this a search routine where you pass in some search criteria, hence the input parameters?

What is the application code that calls the stored procedure? (Please post.)

|||

Arnie,

We have a database that has 4 tables, 25 columns of data to pull from. Software/Hardware data is pulled from the PC's and stored on this database.

The vb.net application I'm creating will connect to a database called "platform_validation_tool" running on SQL 2005, and

will use an SQLConnection, SQLDataAdapter and DataSet to mirror the tables.

Whether the user checks one checkbox or all 25, I want it to pull any column of data based on those checkboxes checked, i.e. using the SQLDataAdapter and it's methods, it will search the database, pass it into the input parameters, and then return it to the DataGrid for display. That is the first step. Right now, I can't get the stored procedure to work because of the datetime datatype to convert to a string. Once I can jump this hurdle, then I'll setup the rest of the code in vb.net. Here's some of the vb.net code that will allow me to retrieve the data from the database by identifying the specific SourceColumn and then Fill the DataGrid:

SqlDA_SinglePkgName.SelectCommand.Parameters.Item(1).Value = SourceColumn

SqlDA_SinglePkgName.SelectCommand.Parameters.Item(2).Value = SourceColumn

etc, and so on.,

SqlDA_SinglePkgName.Fill(WkstnAndSoftwareVerDS1.uspSelectAnyPkgName) --> This is the line of code that calls the stored procedure "uspSelectAnyPkgName".

Let me know if it needs further explanation. I've only been in programming for 3 months. Thank you for your patience.

Wally

|||hi,

i'm just curious on how did you present the values in your checkboxes? a.) is it along side on a data grid? or b.) does your checkbox has an input box along side it where the user can input a search string then ticks the checkbox if it should be included in the search?

if your doing b. does the user need to input the time for the created and modified date?|||How about posting some of the vb.net code that invokes the sp?
SQLDataSource definition, etc.|||

DaleJ,

My code is at work and until I return there, simplest way I can put it is the routine goes like this:

The checkboxes are just inside of a GroupBox on a separate Form and not imbedded within or next to the DataGrid.

If checkbox1.checked = True Then

SqlDA_SinglePkgName.SelectCommand.Parameters.Item(1).Value = SourceColumn

Else checkbox1.checked = False

End If

If checkbox2.checked = True Then

SqlDA_SinglePkgName.SelectCommand.Parameters.Item(2).Value = SourceColumn

Else checkbox2.checked = False Then

End If

Item(1), Item(2), item(3), etc. is the logical order of the column parameters, i.e. Item(0) = @.ReturnValue, Item(1) = @.MAC, Item(2) = @.CREATED, Item(3) = @.MODIFIED, etc.

There will be 25 checkboxes in all, from 4 tables. There's other code that I still need to plug in but this is the jist of it. I just can't get the stored procedure to input any of the data from the MODIFIED and CREATED columns in the database (using DateTime datatype) to convert over to a varchar string.

Finally, after the checkboxes have been evaluated as checked or unchecked, it will run the stored procedure on all the checkboxes whose boolean is True with the Fill method below .

SqlDA_SinglePkgName.Fill(WkstnAndSoftwareVerDS1.uspSelectAnyPkgName) --> This is the line of code that calls the stored procedure "uspSelectAnyPkgName" and fills the DataSet.

It may seem elementary but it's the easiest way for me to start out learning to code until I get a couple of vb.net classes under my belt.

Wallace

|||

The following code example 'should' find a match for any parameters passed in by your users. If this works for you, I suspect that you could do away with the checkboxes on the form AND the IF-End IF blocks -they will not be needed. Just set all the parameters

Since you are starting out learning, I suggest that you quickly drop using all caps. We all have learned to read using mixed case and we recognize and read mixed case with greater ease than all caps. However, there is a 'tradition' of using caps for the SQL language words.

Also, rigorously following good formatting principles will make your code easier to read, and easier to maintain.

Code Snippet

ALTER PROCEDURE [dbo].[uspPvtSelectCommand]
( @.MAC varchar(18),
@.Created datetime,
@.Modified datetime,
@.Workstation_Name varchar(13),
@.IP_Address varchar(15),
@.User_Name varchar(8),
@.Operating_System varchar(25),
@.Service_Pack varchar(3),
@.Band_Version varchar(7),
@.Workstation_OU varchar(200),
@.Workstation_Owner varchar(200),
@.Manufacturer varchar(26),
@.Model varchar(40),
@.Chassis varchar(10),
@.Serial_Number varchar(30),
@.Processor varchar(100),
@.Hard_Drive varchar(40),
@.Hard_Drive_Size varchar(30),
@.Memory varchar(22),
@.Name varchar(100),
@.Version varchar(20),
@.Build varchar(45),
@.Install_Status varchar(20),
@.Install_Date varchar(21),
@.Package_Name varchar(90)
)
AS

SET NOCOUNT ON;

SELECT
m.MAC,
getdate(),
m.Created,
m.Modified,
h.Manufacturer,
h.Model,
h.Chassis,
h.Serial_Number,
h.Processor,
h.Hard_Drive,
h.Hard_Drive_Size,
h.Memory,
n.Workstation_Name,
n.IP_Address,
n.[User_Name],
n.Operating_System,
n.Service_Pack,
n.Band_Version,
n.Workstation_OU,
n.Workstation_Owner,
s.MAC,
s.[Name],
s.Version,
s.Build,
s.Install_Status,
s.Install_Date,
s.Package_Name
FROM Main m
JOIN Hardware h
ON m.MAC = h.MAC
JOIN Network n
ON m.MAC = n.MAC
JOIN Software s
ON m.MAC = s.MAC
WHERE ( m.MAC = @.MAC
AND m.Created = coalesce( nullif( @.Created, 0 ), m.Created )
AND m.Modified = coalesce( nullif( @.Modified, 0 ), m.Modified )
AND n.Workstation_Name = coalesce( nullif( @.Workstation_Name, '' ), n.Workstation_Name )
AND n.IP_Address = coalesce( nullif( @.IP_Address, '' ), n.IP_Address )
AND n.[User_Name] = coalesce( nullif( @.User_Name, '' ), n.[User_Name] )
AND n.Operating_System = coalesce( nullif( @.Operating_System, '' ), n.Operating_System )
AND n.Service_Pack = coalesce( nullif( @.Service_Pack, '' ), n.Service_Pack )
AND n.Band_Version = coalesce( nullif( @.Band_Version, '' ), n.Band_Version )
AND n.Workstation_OU = coalesce( nullif( @.Workstation_OU, '' ), n.Workstation_OU )
AND n.Workstation_Owner = coalesce( nullif( @.Workstation_Owner, '' ), n.Workstation_Owner )
AND h.Manufacturer = coalesce( nullif( @.Manufacturer, '' ), h.Manufacturer )
AND h.Model = coalesce( nullif( @.Model, '' ), h.Model )
AND h.Chassis = coalesce( nullif( @.Chassis, '' ), h.Chassis )
AND h.Serial_Number = coalesce( nullif( @.Serial_Number, '' ), h.Serial_Number )
AND h.Processor = coalesce( nullif( @.Processor, '' ), h.Processor )
AND h.Hard_Drive = coalesce( nullif( @.Hard_Drive, '' ), h.Hard_Drive )
AND h.Hard_Drive_Size = coalesce( nullif( @.Hard_Drive_Size, '' ), h.Hard_Drive_Size )
AND h.Memory = coalesce( nullif( @.Memory, '' ), h.Memory )
AND s.[Name] = coalesce( nullif( @.Name, '' ), s.[Name] )
AND s.Version = coalesce( nullif( @.Version, '' ), s.Version )
AND s.Build = coalesce( nullif( @.Build, '' ), s.Build )
AND s.Install_Status = coalesce( nullif( @.Install_Status, '' ), s.Install_Status )
AND s.Install_Date = coalesce( nullif( @.Install_Date, '' ), s.Install_Date )
AND s.Package_Name = coalesce( nullif( @.Package_Name, '' ), s.Package_Name )

GO

|||

Hey Arnie,

Thank you for the tips. I'll be sure to apply them as a newcomer.

The code you supplied me for the @.CREATED and @.MODIFIED parameters didn't create any errors when I Executed the stored procedure. I went into the datagrid on my vb.net form, clicked on the preview button, and again, it threw me the error, "Enter a value for paramter "CREATED". What I did then was added two dates (format is > 04/04/2007) in the datagrid "value" fields to see what it would return. The results it returned are as follows: Type = Int32, Value = 0. It looks as if it reads the data I inputted into the datagrid value field it recognizes this date format as an Integer but somehow didn't return this data in the results window.

Now, when it hit the 4th line, where I started adding the code you updated, "AND n.Workstation_Name = coalesce( nullif( @.Workstation_Name, '' ), n.Workstation_Name )", in the WHERE clause, it threw this error:

Msg 306, Level 16, State 1, Procedure uspPvtSelectCommand, Line 34

The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

The bottom line is that the stored procedure will not read the date and time info from the CREATED or the MODIFIED fields in the main.MAC table. This is the format in the database for both fields -> 4/25/2007 5:05:04 AM

I'm still trying to find some material on converting this date and time string value to a datetime datatype value.

Any other suggestions are most welcome.

Wally

Read a Database Datetime (datatype) value and pass it into a Parameter

I'm running the following stored proceudre that I will eventually be using checboxes and a sqlDataAdapter to fill a DataGrid using VB.Net.

When I attempt to execute the SP on the server side for testing, it throws me the error "syntax error converting datetime from character string".

conversion of datatypes is something I'm still new to so I can't begin to understand how to write the code thus why I'm seeking help. Here's the SP Code:

As soon as it hits the @.CREATED as datetime =.... this is where it throws that error. Any idea on how to convert the datetime data type to a character string?

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[uspPvtSelectCommand]

@.MAC as varchar(18) = '00:AC:12:E5:76:9C',

@.CREATED as datetime = '4/25/2007 8:40:50 AM',

@.MODIFIED as datetime = '5/19/2007 5:05:04 AM',

@.WORKSTATION_NAME as varchar(13) = 'B000E7FF53C72',

@.IP_ADDRESS as varchar(15) = '171.136.201.142',

@.USER_NAME as varchar(8) = 'nbe5533',

@.OPERATING_SYSTEM as varchar(25) = 'Windows XP Professional',

@.SERVICE_PACK as varchar(3) = '2.0',

@.BAND_VERSION as varchar(7) = '5.06 b2',

@.WORKSTATION_OU as varchar(200) = 'CN=B001321D14A41,OU=Desktops,OU=Agents,OU=Card,OU=Customer Service and Support,OU=Utility,OU=NCG,OU=Workstations,OU=BAND,DC=corp,DC=bankofamerica,DC=com',

@.WORKSTATION_OWNER as varchar(200) = 'CN=Davis\, Wally NBE5533,OU=Distributed Server Support,OU=NCG Administrators,OU=Accounts,OU=BAND,DC=corp,DC=bankofamerica,DC=com',

@.MANUFACTURER as varchar(26) = 'Dell Computer Corporation;',

@.MODEL as varchar(40) = 'Latitude D600;',

@.CHASSIS as varchar(10) = '8;12;',

@.SERIAL_NUMBER as varchar(30) = '.83SFB51.CN486434737027.;',

@.PROCESSOR as varchar(100) = 'Intel(R) Pentium(R) M processor 1600MHz;',

@.HARD_DRIVE as varchar(40) = 'FUJITSU MHV2040AH;',

@.HARD_DRIVE_SIZE as varchar(30) = '40007761920;',

@.MEMORY as varchar(22) = '1073741824;1073741824;',

@.NAME as varchar(100) = 'KB887979',

@.VERSION as varchar(20) = 'VALUE DOES NOT EXIST',

@.BUILD as varchar(45) = '1.3',

@.INSTALL_STATUS as varchar(20) = '1',

@.INSTALL_DATE as varchar(21) = '3/31/2005 2:40:34 PM' ,

@.PACKAGE_NAME as varchar(90) = 'CRYSTAL_REPORTS_ACTIVEX_VIEWER_10.0_9.2_8.6_8.5_20.05.08.01_WKS_XP2KNT_BAND_I1^EDE'

AS

-- SET NOCOUNT ON;

SELECT main.MAC, main.CREATED, main.MODIFIED, hardware.MANUFACTURER, hardware.MODEL, hardware.CHASSIS, hardware.SERIAL_NUMBER, hardware.PROCESSOR,

hardware.HARD_DRIVE, hardware.HARD_DRIVE_SIZE, hardware.MEMORY, network.WORKSTATION_NAME, network.IP_ADDRESS,

network.USER_NAME, network.OPERATING_SYSTEM, network.SERVICE_PACK, network.BAND_VERSION, network.WORKSTATION_OU,

network.WORKSTATION_OWNER, software.MAC, software.NAME, software.VERSION, software.BUILD, software.INSTALL_STATUS,

software.INSTALL_DATE, software.PACKAGE_NAME

FROM main INNER JOIN

hardware ON main.MAC = hardware.MAC INNER JOIN

network ON main.MAC = network.MAC INNER JOIN

software ON main.MAC = software.MAC

WHERE MAIN.MAC LIKE '%'+@.MAC+'%' AND MAIN.CREATED LIKE '%'+@.CREATED+'%' AND MAIN.MODIFIED LIKE '%'+@.MODIFIED+'%' AND NETWORK.WORKSTATION_NAME LIKE '%'+@.WORKSTATION_NAME+'%' AND NETWORK.IP_ADDRESS LIKE '%'+@.IP_ADDRESS+'%' AND NETWORK.USER_NAME LIKE '%'+@.USER_NAME+'%' AND NETWORK.OPERATING_SYSTEM LIKE '%'+@.OPERATING_SYSTEM+'%' AND NETWORK.SERVICE_PACK LIKE '%'+@.SERVICE_PACK+'%' AND NETWORK.BAND_VERSION LIKE '%'+@.BAND_VERSION+'%' AND NETWORK.WORKSTATION_OU LIKE '%'+@.WORKSTATION_OU+'%' AND NETWORK.WORKSTATION_OWNER LIKE '%'+@.WORKSTATION_OWNER+'%' AND HARDWARE.MANUFACTURER LIKE '%'+@.MANUFACTURER+'%' AND HARDWARE.MODEL LIKE '%'+@.MODEL+'%' AND HARDWARE.CHASSIS LIKE '%'+@.CHASSIS+'%' AND HARDWARE.SERIAL_NUMBER LIKE '%'+@.SERIAL_NUMBER+'%' AND HARDWARE.PROCESSOR LIKE '%'+@.PROCESSOR+'%' AND HARDWARE.HARD_DRIVE LIKE '%'+@.HARD_DRIVE+'%' AND HARDWARE.HARD_DRIVE_SIZE LIKE '%'+@.HARD_DRIVE_SIZE+'%' AND HARDWARE.MEMORY LIKE '%'+@.MEMORY+'%' AND SOFTWARE.NAME LIKE '%'+@.NAME+'%' AND SOFTWARE.VERSION LIKE '%'+@.VERSION+'%' AND SOFTWARE.BUILD LIKE '%'+@.BUILD+'%' AND SOFTWARE.INSTALL_STATUS LIKE '%'+@.INSTALL_STATUS+'%' AND SOFTWARE.INSTALL_DATE LIKE '%'+@.INSTALL_DATE+'%' AND SOFTWARE.PACKAGE_NAME LIKE '%'+@.PACKAGE_NAME+'%'

Thank you,

Wallace

hi, this is because you were concatenating a datetime variable with a string '%'

change this lines on your where clause

--AND MAIN.CREATED LIKE '%'+@.CREATED+'%'
AND MAIN.CREATED = @.CREATED
--AND MAIN.MODIFIED LIKE '%'+@.MODIFIED+'%'
AND MAIN.MODIFIED = @.MODIFIED|||

CREATED LIKE '%'+@.CREATED+'%'

Wallace,

I guess that I am at a loss about why you are concatenating wildcards to a datetime value.

Is there a particular problem you are attempting to solve by so doing?

Without converstion, you cannot add characters ( '%' ) to a datetime datatype. (What exactly do you hope to accomplish by adding '%' to the datetime?)

I am assuming that Main.Created is a datetime datetype.

In fact, I wonder if any of the above parameters really need to have '%' added to each side of the value...

|||

As soon as it hits the @.CREATED as datetime =.... this is where it throws that error. Any idea on how to convert the datetime data type to a character string?

Are you doing this like to compare parts of dates? Like:

drop table dateRow
go
create table dateRow
(
dateValue datetime
)
insert into dateRow
select '20070101'
union all
select '20070201'
union all
select '20070301'
union all
select '20070401'
union all
select '20070501'
union all
select '20070601'
go
--find rows from 2007
select *
from dateRow
where convert(varchar(8),datevalue,112) like '2007_'
go
--find rows from January
select *
from dateRow
where convert(varchar(8),datevalue,112) like '_01__'

--find rows from June
select *
from dateRow
where convert(varchar(8),datevalue,112) like '_06__'

Interesting idea...Probably not perfect in terms of performance. A better way to do this involves having a table of dates that you can join to your date value (if you don't have time values.) Then you can index the month, year, or day values for fast searching.

Here is an article with technique to load the date table: http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!1349.entry

|||

This doesn't cause any errors on my server...

Code Snippet

CREATE PROCEDURE dbo.uspPvtSelectCommand
( @.CREATED as datetime = '4/25/2007 8:40:50 AM',
@.MODIFIED as datetime = '5/19/2007 5:05:04 AM'
)
AS
SELECT getdate(), @.Created, @.Modified
GO

I think the error comes from the concatenation of the datetime parameter as I indicated earlier.

LIKE '%'+@.CREATED+'%' AND MAIN.MODIFIED LIKE '%'+@.MODIFIED+'%'

The following fails with such an error...

Code Snippet

ALTER PROCEDURE dbo.uspPvtSelectCommand
( @.CREATED as datetime = '4/25/2007 8:40:50 AM',
@.MODIFIED as datetime = '5/19/2007 5:05:04 AM'
)
AS
SELECT getdate(), @.Created, ( '%' +@.Modified + '%' )
GO

EXECUTE dbo.uspPvtSelectCommand

Server: Msg 241, Level 16, State 1, Procedure uspPvtSelectCommand, Line 6
Conversion failed when converting datetime from character string.

|||

Hi Arnie,

I figured I would give everyone who's been so kind to help with a little more information. I have a DataGrid in my vb.net app.

I have 25 fields/columns and when I go to click on "Preview" from the DataGrid, it hit's the second field parameter "CREATED" and throws the error, " Enter a value for parameter "CREATED". What I have are a bunch of checkboxes on my form, so that our Managers can click on any combination of checkboxs (that represents a field in one of 4 tables), it will pull up only those checkboxex (fields) of that data, use the SP to join those fields and then store it in the datagrid and then a separate sub-routine that exports it to Excel. So, right now, when I go back to recreate a new SQLDataAdapter in vb.net, when I select the stored procedure, it doesn't see the list of parameters, but, it is connected to the right db server so, the problem seems to be the way my Stored procedure is written.

I have since removed the concatenation and changed it so that MAIN.CREATED = @.CREATED AND MAIN.MODIFIED = @.MODIFIED but still the error.

Any further assistance would be appreciated. Here is what the SP looks like now.

USE platform_validation_tool

GO

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[uspPvtSelectCommand]

@.MAC as varchar(18),

@.CREATED as datetime,

@.MODIFIED as datetime,

@.WORKSTATION_NAME as varchar(13),

@.IP_ADDRESS as varchar(15),

@.USER_NAME as varchar(8),

@.OPERATING_SYSTEM as varchar(25),

@.SERVICE_PACK as varchar(3),

@.BAND_VERSION as varchar(7),

@.WORKSTATION_OU as varchar(200),

@.WORKSTATION_OWNER as varchar(200),

@.MANUFACTURER as varchar(26),

@.MODEL as varchar(40),

@.CHASSIS as varchar(10),

@.SERIAL_NUMBER as varchar(30),

@.PROCESSOR as varchar(100),

@.HARD_DRIVE as varchar(40),

@.HARD_DRIVE_SIZE as varchar(30),

@.MEMORY as varchar(22),

@.NAME as varchar(100),

@.VERSION as varchar(20),

@.BUILD as varchar(45),

@.INSTALL_STATUS as varchar(20),

@.INSTALL_DATE as varchar(21),

@.PACKAGE_NAME as varchar(90)

AS

-- SET NOCOUNT ON;

SELECT main.MAC, getdate(), @.CREATED, @.MODIFIED, hardware.MANUFACTURER, hardware.MODEL, hardware.CHASSIS, hardware.SERIAL_NUMBER, hardware.PROCESSOR,

hardware.HARD_DRIVE, hardware.HARD_DRIVE_SIZE, hardware.MEMORY, network.WORKSTATION_NAME, network.IP_ADDRESS,

network.USER_NAME, network.OPERATING_SYSTEM, network.SERVICE_PACK, network.BAND_VERSION, network.WORKSTATION_OU,

network.WORKSTATION_OWNER, software.MAC, software.NAME, software.VERSION, software.BUILD, software.INSTALL_STATUS,

software.INSTALL_DATE, software.PACKAGE_NAME

FROM main INNER JOIN

hardware ON main.MAC = hardware.MAC INNER JOIN

network ON main.MAC = network.MAC INNER JOIN

software ON main.MAC = software.MAC

WHERE MAIN.MAC LIKE '%'+@.MAC+'%' AND MAIN.CREATED = @.CREATED AND MAIN.MODIFIED = @.MODIFIED AND NETWORK.WORKSTATION_NAME LIKE '%'+@.WORKSTATION_NAME+'%' AND NETWORK.IP_ADDRESS LIKE '%'+@.IP_ADDRESS+'%' AND NETWORK.USER_NAME LIKE '%'+@.USER_NAME+'%' AND NETWORK.OPERATING_SYSTEM LIKE '%'+@.OPERATING_SYSTEM+'%' AND NETWORK.SERVICE_PACK LIKE '%'+@.SERVICE_PACK+'%' AND NETWORK.BAND_VERSION LIKE '%'+@.BAND_VERSION+'%' AND NETWORK.WORKSTATION_OU LIKE '%'+@.WORKSTATION_OU+'%' AND NETWORK.WORKSTATION_OWNER LIKE '%'+@.WORKSTATION_OWNER+'%' AND HARDWARE.MANUFACTURER LIKE '%'+@.MANUFACTURER+'%' AND HARDWARE.MODEL LIKE '%'+@.MODEL+'%' AND HARDWARE.CHASSIS LIKE '%'+@.CHASSIS+'%' AND HARDWARE.SERIAL_NUMBER LIKE '%'+@.SERIAL_NUMBER+'%' AND HARDWARE.PROCESSOR LIKE '%'+@.PROCESSOR+'%' AND HARDWARE.HARD_DRIVE LIKE '%'+@.HARD_DRIVE+'%' AND HARDWARE.HARD_DRIVE_SIZE LIKE '%'+@.HARD_DRIVE_SIZE+'%' AND HARDWARE.MEMORY LIKE '%'+@.MEMORY+'%' AND SOFTWARE.NAME LIKE '%'+@.NAME+'%' AND SOFTWARE.VERSION LIKE '%'+@.VERSION+'%' AND SOFTWARE.BUILD LIKE '%'+@.BUILD+'%' AND SOFTWARE.INSTALL_STATUS LIKE '%'+@.INSTALL_STATUS+'%' AND SOFTWARE.INSTALL_DATE LIKE '%'+@.INSTALL_DATE+'%' AND SOFTWARE.PACKAGE_NAME LIKE '%'+@.PACKAGE_NAME+'%'

Thanks and Sincerely,

Wallace

|||

Wallace,

I need you to clarify.

If I understand this correctly, you are passing into the stored procedure the checkbox values for each of the parameters listed. Is that correct?

And what checkboxes are checked determines what data is expected?

OR,

Is there actual values in the input parameters, AND the presence of a check indicated to use that value in the WHERE clause...

|||That's correct, I want to pass values, being read from the database, into the sql sp parameters, based on which checkboxes are checked.

The values will be read from the Database, passed into the parameter, and joined via the SP into a DataGrid. I have a separate vb.net sub routine that will take all of this and export to excel.

Thanks,

Wally

|||

I'm trying to understand and help, but I'm still confused. You replied "That's correct..." to two diametrically opposed questions.

Do you wish to collect multiple rows of data from the database and display that data in the DataGrid?

How do you determine what should be displayed?

Are all columns always returned and displayed?

Is this a search routine where you pass in some search criteria, hence the input parameters?

What is the application code that calls the stored procedure? (Please post.)

|||

Arnie,

We have a database that has 4 tables, 25 columns of data to pull from. Software/Hardware data is pulled from the PC's and stored on this database.

The vb.net application I'm creating will connect to a database called "platform_validation_tool" running on SQL 2005, and

will use an SQLConnection, SQLDataAdapter and DataSet to mirror the tables.

Whether the user checks one checkbox or all 25, I want it to pull any column of data based on those checkboxes checked, i.e. using the SQLDataAdapter and it's methods, it will search the database, pass it into the input parameters, and then return it to the DataGrid for display. That is the first step. Right now, I can't get the stored procedure to work because of the datetime datatype to convert to a string. Once I can jump this hurdle, then I'll setup the rest of the code in vb.net. Here's some of the vb.net code that will allow me to retrieve the data from the database by identifying the specific SourceColumn and then Fill the DataGrid:

SqlDA_SinglePkgName.SelectCommand.Parameters.Item(1).Value = SourceColumn

SqlDA_SinglePkgName.SelectCommand.Parameters.Item(2).Value = SourceColumn

etc, and so on.,

SqlDA_SinglePkgName.Fill(WkstnAndSoftwareVerDS1.uspSelectAnyPkgName) --> This is the line of code that calls the stored procedure "uspSelectAnyPkgName".

Let me know if it needs further explanation. I've only been in programming for 3 months. Thank you for your patience.

Wally

|||hi,

i'm just curious on how did you present the values in your checkboxes? a.) is it along side on a data grid? or b.) does your checkbox has an input box along side it where the user can input a search string then ticks the checkbox if it should be included in the search?

if your doing b. does the user need to input the time for the created and modified date?|||How about posting some of the vb.net code that invokes the sp?
SQLDataSource definition, etc.|||

DaleJ,

My code is at work and until I return there, simplest way I can put it is the routine goes like this:

The checkboxes are just inside of a GroupBox on a separate Form and not imbedded within or next to the DataGrid.

If checkbox1.checked = True Then

SqlDA_SinglePkgName.SelectCommand.Parameters.Item(1).Value = SourceColumn

Else checkbox1.checked = False

End If

If checkbox2.checked = True Then

SqlDA_SinglePkgName.SelectCommand.Parameters.Item(2).Value = SourceColumn

Else checkbox2.checked = False Then

End If

Item(1), Item(2), item(3), etc. is the logical order of the column parameters, i.e. Item(0) = @.ReturnValue, Item(1) = @.MAC, Item(2) = @.CREATED, Item(3) = @.MODIFIED, etc.

There will be 25 checkboxes in all, from 4 tables. There's other code that I still need to plug in but this is the jist of it. I just can't get the stored procedure to input any of the data from the MODIFIED and CREATED columns in the database (using DateTime datatype) to convert over to a varchar string.

Finally, after the checkboxes have been evaluated as checked or unchecked, it will run the stored procedure on all the checkboxes whose boolean is True with the Fill method below .

SqlDA_SinglePkgName.Fill(WkstnAndSoftwareVerDS1.uspSelectAnyPkgName) --> This is the line of code that calls the stored procedure "uspSelectAnyPkgName" and fills the DataSet.

It may seem elementary but it's the easiest way for me to start out learning to code until I get a couple of vb.net classes under my belt.

Wallace

|||

The following code example 'should' find a match for any parameters passed in by your users. If this works for you, I suspect that you could do away with the checkboxes on the form AND the IF-End IF blocks -they will not be needed. Just set all the parameters

Since you are starting out learning, I suggest that you quickly drop using all caps. We all have learned to read using mixed case and we recognize and read mixed case with greater ease than all caps. However, there is a 'tradition' of using caps for the SQL language words.

Also, rigorously following good formatting principles will make your code easier to read, and easier to maintain.

Code Snippet

ALTER PROCEDURE [dbo].[uspPvtSelectCommand]
( @.MAC varchar(18),
@.Created datetime,
@.Modified datetime,
@.Workstation_Name varchar(13),
@.IP_Address varchar(15),
@.User_Name varchar(8),
@.Operating_System varchar(25),
@.Service_Pack varchar(3),
@.Band_Version varchar(7),
@.Workstation_OU varchar(200),
@.Workstation_Owner varchar(200),
@.Manufacturer varchar(26),
@.Model varchar(40),
@.Chassis varchar(10),
@.Serial_Number varchar(30),
@.Processor varchar(100),
@.Hard_Drive varchar(40),
@.Hard_Drive_Size varchar(30),
@.Memory varchar(22),
@.Name varchar(100),
@.Version varchar(20),
@.Build varchar(45),
@.Install_Status varchar(20),
@.Install_Date varchar(21),
@.Package_Name varchar(90)
)
AS

SET NOCOUNT ON;

SELECT
m.MAC,
getdate(),
m.Created,
m.Modified,
h.Manufacturer,
h.Model,
h.Chassis,
h.Serial_Number,
h.Processor,
h.Hard_Drive,
h.Hard_Drive_Size,
h.Memory,
n.Workstation_Name,
n.IP_Address,
n.[User_Name],
n.Operating_System,
n.Service_Pack,
n.Band_Version,
n.Workstation_OU,
n.Workstation_Owner,
s.MAC,
s.[Name],
s.Version,
s.Build,
s.Install_Status,
s.Install_Date,
s.Package_Name
FROM Main m
JOIN Hardware h
ON m.MAC = h.MAC
JOIN Network n
ON m.MAC = n.MAC
JOIN Software s
ON m.MAC = s.MAC
WHERE ( m.MAC = @.MAC
AND m.Created = coalesce( nullif( @.Created, 0 ), m.Created )
AND m.Modified = coalesce( nullif( @.Modified, 0 ), m.Modified )
AND n.Workstation_Name = coalesce( nullif( @.Workstation_Name, '' ), n.Workstation_Name )
AND n.IP_Address = coalesce( nullif( @.IP_Address, '' ), n.IP_Address )
AND n.[User_Name] = coalesce( nullif( @.User_Name, '' ), n.[User_Name] )
AND n.Operating_System = coalesce( nullif( @.Operating_System, '' ), n.Operating_System )
AND n.Service_Pack = coalesce( nullif( @.Service_Pack, '' ), n.Service_Pack )
AND n.Band_Version = coalesce( nullif( @.Band_Version, '' ), n.Band_Version )
AND n.Workstation_OU = coalesce( nullif( @.Workstation_OU, '' ), n.Workstation_OU )
AND n.Workstation_Owner = coalesce( nullif( @.Workstation_Owner, '' ), n.Workstation_Owner )
AND h.Manufacturer = coalesce( nullif( @.Manufacturer, '' ), h.Manufacturer )
AND h.Model = coalesce( nullif( @.Model, '' ), h.Model )
AND h.Chassis = coalesce( nullif( @.Chassis, '' ), h.Chassis )
AND h.Serial_Number = coalesce( nullif( @.Serial_Number, '' ), h.Serial_Number )
AND h.Processor = coalesce( nullif( @.Processor, '' ), h.Processor )
AND h.Hard_Drive = coalesce( nullif( @.Hard_Drive, '' ), h.Hard_Drive )
AND h.Hard_Drive_Size = coalesce( nullif( @.Hard_Drive_Size, '' ), h.Hard_Drive_Size )
AND h.Memory = coalesce( nullif( @.Memory, '' ), h.Memory )
AND s.[Name] = coalesce( nullif( @.Name, '' ), s.[Name] )
AND s.Version = coalesce( nullif( @.Version, '' ), s.Version )
AND s.Build = coalesce( nullif( @.Build, '' ), s.Build )
AND s.Install_Status = coalesce( nullif( @.Install_Status, '' ), s.Install_Status )
AND s.Install_Date = coalesce( nullif( @.Install_Date, '' ), s.Install_Date )
AND s.Package_Name = coalesce( nullif( @.Package_Name, '' ), s.Package_Name )

GO

|||

Hey Arnie,

Thank you for the tips. I'll be sure to apply them as a newcomer.

The code you supplied me for the @.CREATED and @.MODIFIED parameters didn't create any errors when I Executed the stored procedure. I went into the datagrid on my vb.net form, clicked on the preview button, and again, it threw me the error, "Enter a value for paramter "CREATED". What I did then was added two dates (format is > 04/04/2007) in the datagrid "value" fields to see what it would return. The results it returned are as follows: Type = Int32, Value = 0. It looks as if it reads the data I inputted into the datagrid value field it recognizes this date format as an Integer but somehow didn't return this data in the results window.

Now, when it hit the 4th line, where I started adding the code you updated, "AND n.Workstation_Name = coalesce( nullif( @.Workstation_Name, '' ), n.Workstation_Name )", in the WHERE clause, it threw this error:

Msg 306, Level 16, State 1, Procedure uspPvtSelectCommand, Line 34

The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

The bottom line is that the stored procedure will not read the date and time info from the CREATED or the MODIFIED fields in the main.MAC table. This is the format in the database for both fields -> 4/25/2007 5:05:04 AM

I'm still trying to find some material on converting this date and time string value to a datetime datatype value.

Any other suggestions are most welcome.

Wally

Reach SqlServer located another server

Hi,

I have a asp.net website and bind it with sqlserver but this sqlserver is located another server different from the server my website is located. So how can i bind them with eachother.

Thanks in advance...

Fine, just specify that server name (which is diffrent than the server where your web application in) in the connection string:

Server=YourDatabaseServerName;Database=YourDatabaseName;User ID= YourUserID; Password=YourPassword

Check out his for more examples:www.connectionstrings.com

Good luck.

|||

Thanks my friend but i am sorry i forgot to ask my another question about how to connect database if it is different from sqlserver (i mean it can be oracle or something else) located another server.

Thanks in advance again...!

|||

Just visit the link I posted:http://www.connectionstrings.com/

and click on the database that you are using (e.g. Oracle -> usehttp://www.connectionstrings.com/?carrier=oracle ).

Good luck.

RDLC not recognizing DataSet

ASP.NET 2.0 Project
Added
rdlc file
DataSet with DataTable to be populated dynamically at runtime
I cannot set DataSetName in the dropdown -- it does not recognize my
DataSet and I cannot type into it.
If I compile I receive this error for my RDLC
"Error 1 The table 'table1' is in the report body but the report has
no data set. Data regions are not allowed in reports without
datasets."Are you saying that in the "Report Data Sources" window you do not see any
datasets (it actually shows the datatables) in the "Project Data Sources"
dropdown?
Where exactly are you trying to enter this dataset
Do you have an xsd created?
In the datasources window (Which shows when you are looking at a report),
what do you see? And can you create a new datasource from there?
// Andrew
> ASP.NET 2.0 Project
> Added
> rdlc file
> DataSet with DataTable to be populated dynamically at runtime
> I cannot set DataSetName in the dropdown -- it does not recognize my
> DataSet and I cannot type into it.
> If I compile I receive this error for my RDLC
> "Error 1 The table 'table1' is in the report body but the report has
> no data set. Data regions are not allowed in reports without
> datasets."
>

RDLC and Excel - No Grid Lines

Using ASP.NET 2, C#, Web application, we have an rdlc report which will be primarily used to export to an Excel spreadsheet. When we run thw report, then export it, we wind up with a spreadsheet with no gridlines, eg a spreadsheet with invisible cell borders.

Is the a setting or property somewhere which can be changed so that the spreadsheet which opens after the export looks like a standard spreadsheet, that is, with visible cell borders?

Many thanks
Mike Thomas

In the test I just did, setting the BorderStyle to Single on the text boxes I wanted to have borders worked. You should also take a look at http://msdn2.microsoft.com/en-us/library/aa178951(SQL.80).aspx to see how cells are converted. Short and sweet: best, use a table; next best, make sure to align everything.

Larry

|||

Sorry, it's late. I should have also mentioned that every cell in a table and a matrix are textboxes. The BorderStyle is 'none' by default and the BorderColor is 'Black' by default. The properties I mean are those in Visual Studio, not those found through the context menu.

Larry

rdl generator

HI all
im new to sql reporting services, im in need of a rdl (report definition language)generator
are there any resouirces out there on the net ?
if so , can u suggest some links for the same?
thanks in advance !

Hi,

I do not really know if I am interpreting your question in the right way..

You are not searching for a tool like the "Business Intelligence Development Studio" which is included in the Client Installation of SQL 2005 and is the tool for developing projects for SSIS, SSAS and SSRS...

If you want to develope your own report generator you could use this link and code http://msdn2.microsoft.com/en-us/library/ms170239.aspx as a start point.

cheers,
Markus

|||

This one I think it best: http://msdn2.microsoft.com/en-us/library/ms170667.aspx

Here are a few others:

http://www.codeproject.com/csharp/rdlproject.asp

http://blogs.msdn.com/swisowaty/archive/2006/06/07/620838.aspx

|||

thanks ??€?§Q? for your response... i had managed to come across those links and build the rdl generator

follow up:

what we actually need was a custom report designer that would give the corresponding rdl file of the user specified format

so the rdl generator was just a small part ..

so any ideas/clues /suggestions are very welcome , pls help !

|||thanks markus for that link , but i had already come up with that one...and no we are not supposed to use the report builder in the ssrs package .|||

kuruvilla wrote:

a custom report designer that would give the corresponding rdl file of the user specified format

What do you mean by this?

|||

Hi,

instead of programming a new softwareon your own you probably should give rsinteract [www.rsinteract.com] a try...

cheers

Markus

|||well what i meant was the same utilities which u have with the reporrt builder , is what is needed but maybe not as diverse and feature rich as we have in the report builder tool
something which the customers can use
in case u wanna know the details they are AND MOST IMPORTANTLY IT SHOULD BE WINDOWS BASED NOT A WEB APPLICATION:-

o follows a Wizard pattern to generate Report

o Tree-view listing of Reports, Reports can be grouped and sub-grouped

o Wizard navigates treads the following path, Select a table->Brings up Related Tables->Field Selection->Manipulate Field (Can select a field, for query generation, but opt to not print it)->Multiple Sorts->Filtering of Data

The above procedure acts as a Query Generator and generates a Query to be executed to retrieve data

o Report Designer, a canvas area to decide the layout of the Report. Actual Report generation is assisted by a third-party tool.

o Fields on Report Designer can be formatted using a context menu

o Formula option provides complete flexibility in defining a formula field More details required

o No Sub-Reports

o Data entry forms are linked to fields on the Reports, user can click on a field and a Form is opened with relevant data

o Report is divided in different section Report Header, Page Header, Section Header etc.

o Tree-view of the Controls Structure, placed on the canvas can be seen. The structure is editable through this feature

o Drawing tools are provided to be used on the canvas

o Image of different formats can be placed on the canvas

o Text boxes and Font can be formatted

o Page Setup, Paper Size for the Report can be defined

o Status bar provides information while dragging fields and on mouse-over for the formula fields

o Report can be exported to various formats Excel, HTML, DBF, CSV, PDF

o Report can be send via an Email. It can be send as a PDF attachment, as an attachment in Custom format with a Custom reader. (Custom format not required now)

o Reports can be compressed and send via an Email

o Snapped Grid Context-sensitive help

|||THANKS MARCKUS..well thats just about what i needed i guess, but not as a web app, but i need it to be embedded in the windows application

rdl generator

HI all
im new to sql reporting services, im in need of a rdl (report definition language)generator
are there any resouirces out there on the net ?
if so , can u suggest some links for the same?
thanks in advance !

Hi,

I do not really know if I am interpreting your question in the right way..

You are not searching for a tool like the "Business Intelligence Development Studio" which is included in the Client Installation of SQL 2005 and is the tool for developing projects for SSIS, SSAS and SSRS...

If you want to develope your own report generator you could use this link and code http://msdn2.microsoft.com/en-us/library/ms170239.aspx as a start point.

cheers,
Markus

|||

This one I think it best: http://msdn2.microsoft.com/en-us/library/ms170667.aspx

Here are a few others:

http://www.codeproject.com/csharp/rdlproject.asp

http://blogs.msdn.com/swisowaty/archive/2006/06/07/620838.aspx

|||

thanks ??€?§Q? for your response... i had managed to come across those links and build the rdl generator

follow up:

what we actually need was a custom report designer that would give the corresponding rdl file of the user specified format

so the rdl generator was just a small part ..

so any ideas/clues /suggestions are very welcome , pls help !

|||thanks markus for that link , but i had already come up with that one...and no we are not supposed to use the report builder in the ssrs package .|||

kuruvilla wrote:

a custom report designer that would give the corresponding rdl file of the user specified format

What do you mean by this?

|||

Hi,

instead of programming a new softwareon your own you probably should give rsinteract [www.rsinteract.com] a try...

cheers

Markus

|||well what i meant was the same utilities which u have with the reporrt builder , is what is needed but maybe not as diverse and feature rich as we have in the report builder tool
something which the customers can use
in case u wanna know the details they are AND MOST IMPORTANTLY IT SHOULD BE WINDOWS BASED NOT A WEB APPLICATION:-

o follows a Wizard pattern to generate Report

o Tree-view listing of Reports, Reports can be grouped and sub-grouped

o Wizard navigates treads the following path, Select a table->Brings up Related Tables->Field Selection->Manipulate Field (Can select a field, for query generation, but opt to not print it)->Multiple Sorts->Filtering of Data

The above procedure acts as a Query Generator and generates a Query to be executed to retrieve data

o Report Designer, a canvas area to decide the layout of the Report. Actual Report generation is assisted by a third-party tool.

o Fields on Report Designer can be formatted using a context menu

o Formula option provides complete flexibility in defining a formula field More details required

o No Sub-Reports

o Data entry forms are linked to fields on the Reports, user can click on a field and a Form is opened with relevant data

o Report is divided in different section Report Header, Page Header, Section Header etc.

o Tree-view of the Controls Structure, placed on the canvas can be seen. The structure is editable through this feature

o Drawing tools are provided to be used on the canvas

o Image of different formats can be placed on the canvas

o Text boxes and Font can be formatted

o Page Setup, Paper Size for the Report can be defined

o Status bar provides information while dragging fields and on mouse-over for the formula fields

o Report can be exported to various formats Excel, HTML, DBF, CSV, PDF

o Report can be send via an Email. It can be send as a PDF attachment, as an attachment in Custom format with a Custom reader. (Custom format not required now)

o Reports can be compressed and send via an Email

o Snapped Grid Context-sensitive help

|||THANKS MARCKUS..well thats just about what i needed i guess, but not as a web app, but i need it to be embedded in the windows application

RDL File Not Saved

I am using Visual Studio .NET 2003 (with Reporting Services installed), and a
cluster of SQL Server 2000 in a Citrix environment.
I created a Business Inteligence Project, a Shared data source, added a new
report and created a new dataset. I added tables to my Data tab, setup the
relationships, etc.
I Save All and exit VS (without building or deploying the solution). Next
time I come back my Report Data file does not contain the tables I previously
added on. No matter how many tables I add the size of the .rdl file seems to
be 1 kB.
This hapens occasionally only. I could not find a relationship yet but there
are occasions when the rdl file does save properly and thus I don't loose all
my work.
Any suggestions would be greatly appreciated!
Thanks
SorinTry to switch to Layout view before saving your project.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"SOPONL" <SOPONL@.discussions.microsoft.com> wrote in message
news:891ADFC9-F003-4789-94E6-85214AF9AF02@.microsoft.com...
>I am using Visual Studio .NET 2003 (with Reporting Services installed), and
>a
> cluster of SQL Server 2000 in a Citrix environment.
> I created a Business Inteligence Project, a Shared data source, added a
> new
> report and created a new dataset. I added tables to my Data tab, setup the
> relationships, etc.
> I Save All and exit VS (without building or deploying the solution). Next
> time I come back my Report Data file does not contain the tables I
> previously
> added on. No matter how many tables I add the size of the .rdl file seems
> to
> be 1 kB.
> This hapens occasionally only. I could not find a relationship yet but
> there
> are occasions when the rdl file does save properly and thus I don't loose
> all
> my work.
> Any suggestions would be greatly appreciated!
> Thanks
> Sorin|||I've switched to Layout, saved the project and closed VS. Then I re-open the
project; the tables were still not there.
"Lev Semenets [MSFT]" wrote:
> Try to switch to Layout view before saving your project.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "SOPONL" <SOPONL@.discussions.microsoft.com> wrote in message
> news:891ADFC9-F003-4789-94E6-85214AF9AF02@.microsoft.com...
> >I am using Visual Studio .NET 2003 (with Reporting Services installed), and
> >a
> > cluster of SQL Server 2000 in a Citrix environment.
> >
> > I created a Business Inteligence Project, a Shared data source, added a
> > new
> > report and created a new dataset. I added tables to my Data tab, setup the
> > relationships, etc.
> >
> > I Save All and exit VS (without building or deploying the solution). Next
> > time I come back my Report Data file does not contain the tables I
> > previously
> > added on. No matter how many tables I add the size of the .rdl file seems
> > to
> > be 1 kB.
> >
> > This hapens occasionally only. I could not find a relationship yet but
> > there
> > are occasions when the rdl file does save properly and thus I don't loose
> > all
> > my work.
> >
> > Any suggestions would be greatly appreciated!
> >
> > Thanks
> > Sorin
>
>|||In the Data tab I always click in the top section until I see the "*" after
the file name and then right-click the tab to save it.
"SOPONL" wrote:
> I've switched to Layout, saved the project and closed VS. Then I re-open the
> project; the tables were still not there.
> "Lev Semenets [MSFT]" wrote:
> > Try to switch to Layout view before saving your project.
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "SOPONL" <SOPONL@.discussions.microsoft.com> wrote in message
> > news:891ADFC9-F003-4789-94E6-85214AF9AF02@.microsoft.com...
> > >I am using Visual Studio .NET 2003 (with Reporting Services installed), and
> > >a
> > > cluster of SQL Server 2000 in a Citrix environment.
> > >
> > > I created a Business Inteligence Project, a Shared data source, added a
> > > new
> > > report and created a new dataset. I added tables to my Data tab, setup the
> > > relationships, etc.
> > >
> > > I Save All and exit VS (without building or deploying the solution). Next
> > > time I come back my Report Data file does not contain the tables I
> > > previously
> > > added on. No matter how many tables I add the size of the .rdl file seems
> > > to
> > > be 1 kB.
> > >
> > > This hapens occasionally only. I could not find a relationship yet but
> > > there
> > > are occasions when the rdl file does save properly and thus I don't loose
> > > all
> > > my work.
> > >
> > > Any suggestions would be greatly appreciated!
> > >
> > > Thanks
> > > Sorin
> >
> >
> >

rda.pull exception

Hi,

I have followed the technical artical "Remote Data Access Synchronization with SQL Server 2005 Compact Edition and Visual Basic.NET" to create a sample application.

But when I run debug mode and get an exception unexpected as following as doing rad.pull :

Immediate Window:

A first chance exception of type 'System.Data.SqlServerCe.SqlCeException' occurred in System.Data.SqlServerCe.dll

Message Box

[Contacts]

Code:

Private Sub RADPullButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RADPullButton.Click

Try
VerifyDatabaseExists()
Dim rda As SqlCeRemoteDataAccess
rda = GetRDAObject()
rda.Pull("Contacts", _
"SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers", _
My.Settings.ServerOleDBNorthwindConnectionString, _
RdaTrackOption.TrackingOnWithIndexes, _
"Contacts_Errors")

Catch ex As Exception
MessageBox.Show(ex.ToString())
Finally
Windows.Forms.Cursor.Current = Cursors.Default
End Try

End Sub

Thanks in advance,

JD

You need to grab all the error information, by following the guidelines in this topic from SQL CE Books online - Handling Errors in Managed Applications http://msdn2.microsoft.com/en-us/library/ms174079.aspx

|||

HI ErikEJ,

I have following the link to catch real sql server compact edition error exception

Here is what I got and I still don't know what this meas:

Error Code:80004005
Message :[Contacts]
Minor Err: 28573
Source: Microsoft SQL Server Compact Edition
Err. Par.: Contacts

Thanks a lot,

JD

|||

There already is a local table with the same name as used in your RDA definition. See this very helpful post by laxmi at http://blogs.msdn.com/sqlservercompact/archive/2007/01/23/rda-subscriptions.aspx

|||

I am having a similar problem. When I pull the tables, I also create an error table (tablename_err). If I have to pull a table again, I first drop the table if it exists and then attempt to pull it again. I cannot do this though because when I drop the table, the error table does not get dropped and it will not let me pull a another error table (error 28573). I have attempted to explicitly drop the error table, but I am not allowed to do that either as that generates the 28605 error: "DDL operations on the table are restricted." I did not have this problem with SQLCE 2.0, only since I migrated to SQLMobile (3.0). How do I get rid of the original error tables so that I can pull again?

|||

One doesn't has to drop the error tables explicitly. These will be dropped automatically when the associated RDA tracked table is dropped. For e.g.:

rda.pull(@."pulltable", @."select * from foo", <conn string to SQL server>, RDATrackOption.TrackingOn, @."err_pull");

when one drops pulltable, err_pull automatically gets dropped since it is associated with pulltable which is being tracked. Please refer to "Maintaining Error Tables" in http://msdn2.microsoft.com/en-us/library/ms171782.aspx

Could you verify if you are indeed dropping all the local data tables into which you are attempting to pull data?

rda.pull exception

Hi,

I have followed the technical artical "Remote Data Access Synchronization with SQL Server 2005 Compact Edition and Visual Basic.NET" to create a sample application.

But when I run debug mode and get an exception unexpected as following as doing rad.pull :

Immediate Window:

A first chance exception of type 'System.Data.SqlServerCe.SqlCeException' occurred in System.Data.SqlServerCe.dll

Message Box

[Contacts]

Code:

Private Sub RADPullButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RADPullButton.Click

Try
VerifyDatabaseExists()
Dim rda As SqlCeRemoteDataAccess
rda = GetRDAObject()
rda.Pull("Contacts", _
"SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers", _
My.Settings.ServerOleDBNorthwindConnectionString, _
RdaTrackOption.TrackingOnWithIndexes, _
"Contacts_Errors")

Catch ex As Exception
MessageBox.Show(ex.ToString())
Finally
Windows.Forms.Cursor.Current = Cursors.Default
End Try

End Sub

Thanks in advance,

JD

You need to grab all the error information, by following the guidelines in this topic from SQL CE Books online - Handling Errors in Managed Applications http://msdn2.microsoft.com/en-us/library/ms174079.aspx

|||

HI ErikEJ,

I have following the link to catch real sql server compact edition error exception

Here is what I got and I still don't know what this meas:

Error Code:80004005
Message :[Contacts]
Minor Err: 28573
Source: Microsoft SQL Server Compact Edition
Err. Par.: Contacts

Thanks a lot,

JD

|||

There already is a local table with the same name as used in your RDA definition. See this very helpful post by laxmi at http://blogs.msdn.com/sqlservercompact/archive/2007/01/23/rda-subscriptions.aspx

|||

I am having a similar problem. When I pull the tables, I also create an error table (tablename_err). If I have to pull a table again, I first drop the table if it exists and then attempt to pull it again. I cannot do this though because when I drop the table, the error table does not get dropped and it will not let me pull a another error table (error 28573). I have attempted to explicitly drop the error table, but I am not allowed to do that either as that generates the 28605 error: "DDL operations on the table are restricted." I did not have this problem with SQLCE 2.0, only since I migrated to SQLMobile (3.0). How do I get rid of the original error tables so that I can pull again?

|||

One doesn't has to drop the error tables explicitly. These will be dropped automatically when the associated RDA tracked table is dropped. For e.g.:

rda.pull(@."pulltable", @."select * from foo", <conn string to SQL server>, RDATrackOption.TrackingOn, @."err_pull");

when one drops pulltable, err_pull automatically gets dropped since it is associated with pulltable which is being tracked. Please refer to "Maintaining Error Tables" in http://msdn2.microsoft.com/en-us/library/ms171782.aspx

Could you verify if you are indeed dropping all the local data tables into which you are attempting to pull data?

rda.pull exception

Hi,

I have followed the technical artical "Remote Data Access Synchronization with SQL Server 2005 Compact Edition and Visual Basic.NET" to create a sample application.

But when I run debug mode and get an exception unexpected as following as doing rad.pull :

Immediate Window:

A first chance exception of type 'System.Data.SqlServerCe.SqlCeException' occurred in System.Data.SqlServerCe.dll

Message Box

[Contacts]

Code:

Private Sub RADPullButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RADPullButton.Click

Try
VerifyDatabaseExists()
Dim rda As SqlCeRemoteDataAccess
rda = GetRDAObject()
rda.Pull("Contacts", _
"SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers", _
My.Settings.ServerOleDBNorthwindConnectionString, _
RdaTrackOption.TrackingOnWithIndexes, _
"Contacts_Errors")

Catch ex As Exception
MessageBox.Show(ex.ToString())
Finally
Windows.Forms.Cursor.Current = Cursors.Default
End Try

End Sub

Thanks in advance,

JD

You need to grab all the error information, by following the guidelines in this topic from SQL CE Books online - Handling Errors in Managed Applications http://msdn2.microsoft.com/en-us/library/ms174079.aspx

|||

HI ErikEJ,

I have following the link to catch real sql server compact edition error exception

Here is what I got and I still don't know what this meas:

Error Code:80004005
Message :[Contacts]
Minor Err: 28573
Source: Microsoft SQL Server Compact Edition
Err. Par.: Contacts

Thanks a lot,

JD

|||

There already is a local table with the same name as used in your RDA definition. See this very helpful post by laxmi at http://blogs.msdn.com/sqlservercompact/archive/2007/01/23/rda-subscriptions.aspx

|||

I am having a similar problem. When I pull the tables, I also create an error table (tablename_err). If I have to pull a table again, I first drop the table if it exists and then attempt to pull it again. I cannot do this though because when I drop the table, the error table does not get dropped and it will not let me pull a another error table (error 28573). I have attempted to explicitly drop the error table, but I am not allowed to do that either as that generates the 28605 error: "DDL operations on the table are restricted." I did not have this problem with SQLCE 2.0, only since I migrated to SQLMobile (3.0). How do I get rid of the original error tables so that I can pull again?

|||

One doesn't has to drop the error tables explicitly. These will be dropped automatically when the associated RDA tracked table is dropped. For e.g.:

rda.pull(@."pulltable", @."select * from foo", <conn string to SQL server>, RDATrackOption.TrackingOn, @."err_pull");

when one drops pulltable, err_pull automatically gets dropped since it is associated with pulltable which is being tracked. Please refer to "Maintaining Error Tables" in http://msdn2.microsoft.com/en-us/library/ms171782.aspx

Could you verify if you are indeed dropping all the local data tables into which you are attempting to pull data?

RDA with Linked Servers

I am developing a WM5 program (C#.NET) using SQL Server Mobile, using RDA to pull and push data from/to SQL2005.

The question/issue is this: Does RDA work with linked servers, more specifically, if the RDA table is an Oracle table accessed through a linked server? I have a linked server defined in SQL2005, using the .NET Oracle provider (MSDAORA) linked to Oracle 10g. When I try to perform an RDA PULL operation from Windows Mobile 5 (Smartphone emulator) I get the following error:

“The query cannot be tracked. There might not be a primary key, or the query might involve multiple tables. [ Query string = SELECT * FROM DATABASE_NAME..SCHEMA_NAME.TABLE_NAME ]”

I have used at least 3 different test tables in Oracle, trying different options. The error message above implies that the table may not have a primary key but it does (constraint and index) and I have tried a few options with this. The message also implies that multiple tables may be involved but this is not the case either. I have tried variations on the select statement including specifying the columns. I tried building the CREATE TABLE statements with the PK using SQLServer to generate the SQL DDL to see if SQLServer might build the table, constraint, index with options the way it wants to see them – same result.

I can run most any queries (select, insert, etc.) from SQLServer using the linked server and the queries work. The only problems involve RDA to the linked server.

I tried turning tracking off on RDA and was able to pull the entire table from the linked server. However, RDA requires that you can only push a tracked table (change tracking on). I tried to push a non-tracked table and it fails.

Make sure that the table you are referring to is under DBO schema. If it is under any other schema, then RDA might be encountering problem in finding the Primary Key for that table.

Let me know the details after your trials!

Thanks,

Laxmi Narsimha Rao ORUGANTI, SQL Server Everywhere, Microsoft Corporation

|||

All of the objects in the SQL2005 database are in the dbo schema. However, the query from RDA pull references an Oracle table in a named schema, using 4-part name notation "SELECT * FROM DATABASE_NAME..SCHEMA_NAME.TABLE_NAME " per the SQL2005 distributed query guidelines. In this case, the credentials for the linked server Oracle access are for the owner of the named schema.

Thanks,

MattR-KS

|||

Hi Matt,

Just to narrow down the problem. What happens when you are pulling a table in a named schema from SQL Server than from oracle via linked SQL Server. What I mean is have the table in a name schema in SQL Server itself and do a RDA with that.

Thanks,

Laxmi Narsimha Rao ORUGANTI, SQL Server Everywhere, Microsoft Corporation

RDA SQL MOBILE ERROR 28037 HResult = -2147012867

I am modifying the aplication to visual.net from visual studio 2005 but I have problems ( in this moment i working in my pc , with sql 2000 personal ,windows xp , sql mobile and pc pocket emulator for window ce 5.0)

I don't know but i can't make the Pull in the server.

1) the http://localhost/driver2005/sqlcesa30.dll?diag is correct

2) I don't have the windows firewall

3)the anonimous user have all the permition in the virtual directory..

Where is the error ?

Thanks

Have you granted permissions on the database itself for the anonymous IIS account (IUSR_{yourservername}) ?

You are experiencing a permissions problem between the ISAPI DLL that brokers RDA (sqlcesa30.dll) and SQL Server with this error.

-Darren

|||

Yes,in the IIS the ISAPI dll are allows and the anonymous account has total access in th database.

the problems continue, but in this moment the error is the 28017 with the Message = "An incorrect or unsupported HTTP function call was made."

Thanks

|||I found the solution . The problems was that the sqlcesa30.dll must be int the wwwroot directory.|||I am also facing the same problem, infact i copied the dll file into wwwroot directory but still getting the same error 'in correct or unsupported HTTP function call was made'.
Can you please help me ...
this error is quite frustating

RDA SQL MOBILE ERROR 28037 HResult = -2147012867

I am modifying the aplication to visual.net from visual studio 2005 but I have problems ( in this moment i working in my pc , with sql 2000 personal ,windows xp , sql mobile and pc pocket emulator for window ce 5.0)

I don't know but i can't make the Pull in the server.

1) the http://localhost/driver2005/sqlcesa30.dll?diag is correct

2) I don't have the windows firewall

3)the anonimous user have all the permition in the virtual directory..

Where is the error ?

Thanks

Have you granted permissions on the database itself for the anonymous IIS account (IUSR_{yourservername}) ?

You are experiencing a permissions problem between the ISAPI DLL that brokers RDA (sqlcesa30.dll) and SQL Server with this error.

-Darren

|||

Yes,in the IIS the ISAPI dll are allows and the anonymous account has total access in th database.

the problems continue, but in this moment the error is the 28017 with the Message = "An incorrect or unsupported HTTP function call was made."

Thanks

|||I found the solution . The problems was that the sqlcesa30.dll must be int the wwwroot directory.|||I am also facing the same problem, infact i copied the dll file into wwwroot directory but still getting the same error 'in correct or unsupported HTTP function call was made'.
Can you please help me ...
this error is quite frustating