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
No comments:
Post a Comment