Check my SQL

[BlackRiver]

Can anyone spot something wrong with the way this sql is written?

Error says: Conversion failed when converting nvarchar value ' ' ' to data type int

Where's that value? I can't see ' ' '

connTrans.Execute ("EXECUTE " & executeCommand & " '" & txtBusinessPartnerCode.Text & "', ' '' ', '" & _
                 txtBusinessPartnerName.Text & "', '" & businessPartnerTypeSelection & "', '" & _
                 txtBusinessPartnerJMBG.Text & "', '" & _
                 txtBusinessPartnerBLK.Text & "', ' '' ', ' '' ', ' '' ', ' '' ', ' '' ', ' '' ', ' '' ', ' '' ', ' '' ', ' '' ', ' '' ', ' '' ', ' '' ', ' '' ', ' '' ', ' '' ', ' '' ', ' '' ', ' '' ', ' '' ', ' '' ', '" & userName & "'")

[RhinoBull]

What is it that you execute? Stored procedure? if so then check what's going on in there.

[BlackRiver]

Yes it is a stored procedure.And it bugs me because it works fine when executed from Management Studio.

[si_the_geek]

Thread moved to 'Database Development' forum - which is where you should always post SQL based questions (while SQL can be used in VB, it is certainly not specific to VB)


You have multiple instances of: , ' '' ', (which means a value of a single quote with space before and after), and so each one of those could potentially lead to the error you are getting, as single quotes are added around the value that gave an error.

As to which one it is, that depends on the stored procedure.

[RhinoBull]

Can you post your sp header?

[BlackRiver]

Here's the entire SP:

USE [dipis]
GO
/****** Object:  StoredProcedure [dbo].[businessPartnerInsert]    Script Date: 08/02/2010 18:28:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[businessPartnerInsert] 
@businessPartnerCode nvarchar(10),
@businessPartnerNameShort nvarchar(20),
@businessPartnerName nvarchar(200),
@businessPartnerTypeCode int,
@businessPartnerJMBG nvarchar (50),
@businessPartnerIDNumber nvarchar (50),
@businessPartnerStreetNumber nvarchar(20),
@businessPartnerStreetName nvarchar(100),
@countryCode nvarchar(3),
@townCode nvarchar(10),
@businessPartnerTelephone nvarchar(50),
@businessPartnerMobile nvarchar(50), @businessPartnerEmail nvarchar(100),
@businessPartnerWebSite nvarchar(200), @businessPartnerContactPerson nvarchar(100),

@ownershipTypeCode nvarchar(20), 
@relatedLegalEntityCode nvarchar(2),
@businessPartnerUniqueIdentificationNumber nvarchar(50),
@businessPartnerTaxNumber nvarchar(50),
@taxCode nvarchar(20),
@taxAreaCode nvarchar(10), 

@businessPartnerCourtRegistration nvarchar(250),
@businessPartnerCourtRegistrationNumber nvarchar(50), 
@businessPartnerActivityCode nvarchar(10),
@marketCode nvarchar(10), @businessPartnerOwnerCode nvarchar(10),
@businessPartnerActive nvarchar(1), @userName nvarchar(20)

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	IF @businessPartnerNameShort = '' SET @businessPartnerNameShort = NULL
	IF @businessPartnerName = '' SET @businessPartnerName = NULL
	IF @businessPartnerTypeCode = '' SET @businessPartnerTypeCode = NULL
	IF @businessPartnerJMBG = '' SET @businessPartnerJMBG = NULL
	IF @businessPartnerIDNumber = '' SET @businessPartnerIDNumber = NULL
	IF @businessPartnerStreetNumber = '' SET @businessPartnerStreetNumber = NULL
	IF @businessPartnerStreetName = '' SET @businessPartnerStreetName = NULL
	IF @countryCode = '' SET @countryCode = NULL
	IF @townCode = '' SET @townCode = NULL
	IF @businessPartnerTelephone = '' SET @businessPartnerTelephone = NULL
	IF @businessPartnerMobile = '' SET @businessPartnerMobile = NULL
	IF @businessPartnerEmail = '' SET @businessPartnerEmail = NULL
	IF @businessPartnerWebSite = '' SET @businessPartnerWebSite = NULL
	IF @businessPartnerContactPerson = '' SET @businessPartnerContactPerson = NULL
	IF @ownershipTypeCode = '' SET @ownershipTypeCode = NULL

	IF @relatedLegalEntityCode = '' SET @relatedLegalEntityCode = NULL
	IF @businessPartnerUniqueIdentificationNumber = '' SET @businessPartnerUniqueIdentificationNumber = NULL
	IF @businessPartnerTaxNumber = '' SET @businessPartnerTaxNumber = NULL

	IF @taxCode = '' SET @taxCode = NULL
	IF @taxAreaCode = '' SET @taxAreaCode = NULL
	IF @businessPartnerCourtRegistration = '' SET @businessPartnerCourtRegistration = NULL
	IF @businessPartnerCourtRegistrationNumber = '' SET @businessPartnerCourtRegistrationNumber = NULL
	IF @businessPartnerActivityCode = '' SET @businessPartnerActivityCode = NULL
	IF @marketCode = '' SET @marketCode = NULL
	IF @businessPartnerOwnerCode = '' SET @businessPartnerOwnerCode = NULL
	IF @businessPartnerActive = '' SET @businessPartnerActive = NULL
	IF @userName = '' SET @userName = NULL
	
	BEGIN TRANSACTION
	INSERT INTO businessPartner 
				(
				businessPartnerCode, businessPartnerNameShort, businessPartnerName, businessPartnerTypeCode, 
				businessPartnerJMBG, businessPartnerIDNumber,
				businessPartnerStreetNumber, businessPartnerStreetName, countryCode, townCode,
				businessPartnerTelephone, businessPartnerMobile, businessPartnerEmail, businessPartnerWebSite,
				businessPartnerContactPerson, ownershipTypeCode, 
				relatedLegalEntityCode, businessPartnerUniqueIdentificationNumber, 
				businessPartnerTaxNumber,
				taxCode, taxAreaCode,
				businessPartnerCourtRegistration, businessPartnerCourtRegistrationNumber, businessPartnerActivityCode, marketCode,
				businessPartnerOwnerCode, businessPartnerActive, 
				createUserName, createDateTime, lastModifyUserName, lastModifyDateTime
				)
	VALUES		
				(
				@businessPartnerCode, @businessPartnerNameShort, @businessPartnerName, @businessPartnerTypeCode,
				@businessPartnerJMBG, @businessPartnerIDNumber,
				@businessPartnerStreetNumber, @businessPartnerStreetName, @countryCode, @townCode,
				@businessPartnerTelephone, @businessPartnerMobile, @businessPartnerEmail, @businessPartnerWebSite,
				@businessPartnerContactPerson, @ownershipTypeCode, 
				@relatedLegalEntityCode, @businessPartnerUniqueIdentificationNumber, 
				@businessPartnerTaxNumber,
				@taxCode, @taxAreaCode,
				@businessPartnerCourtRegistration, @businessPartnerCourtRegistrationNumber, @businessPartnerActivityCode, @marketCode,
				@businessPartnerOwnerCode, @businessPartnerActive, 
				@userName, GETDATE(), @userName, GETDATE()
				)
		
	IF @@error <> 0
	BEGIN
		ROLLBACK
		RAISERROR ('PROBLEM', 16, 1)
		RETURN
	END

	COMMIT

END

[si_the_geek]

The fourth parameter is this:

@businessPartnerTypeCode int,

..and this is what you are passing it:

'" & businessPartnerTypeSelection & "',

I have no idea what value is in the businessPartnerTypeSelection variable, but as a minimum the single quotes should clearly be removed.

[BlackRiver]

That variable is declared as Integer.I have also noticed what you pointed out, but after I remove the single quotes, that doesn't solve the error.

connTrans.Execute ("EXECUTE " & executeCommand & " '" & txtBusinessPartnerCode.Text & "',' '' ','" & _
                 txtBusinessPartnerName.Text & "'," & businessPartnerTypeSelection & ",'" & _
                 txtBusinessPartnerJMBG.Text & "','" & _
                 txtBusinessPartnerBLK.Text & "',' '' ',' '' ',' '' ',' '' ',' '' ',' '' ',' '' ',' '' ',' '' ',' '' ',' '' ',' '' ',' '' ',' '' ',' '' ',' '' ',' '' ',' '' ',' '' ',' '' ',' '' ','" & userName & "'")

The thing is, I did not design the database, I just make use of stored procedures and when something like this happens,I usually have two choices: Try and figure it out myself or meet the person responsable for the design. Today I decided to go with the first choice, but looks like it's not working

Here's what the actual error says:

[si_the_geek]

I can't see anything else at the moment.

Rather than building and running the SQL statement in one go, do it in two steps - build it in a variable, and then run it. This will allow you to check the value you have built (eg: Debug.Print strSQL ), which may make the issue obvious to you, and will allow you to show it to us.

[techgnome]

I realize this is going to sound like a stupid question, but I'm going to ask anyways.... is there a reason for not using a Command object... and parameters? I realize it might seen silly and naive of me, but seems like it would be a whole lot simple to use a command object, and add parameters to it.

-tg

[BlackRiver]

I realize this is going to sound like a stupid question, but I'm going to ask anyways.... is there a reason for not using a Command object... and parameters? I realize it might seen silly and naive of me, but seems like it would be a whole lot simple to use a command object, and add parameters to it.

-tg

It's a valid question, and that would be my way of handling this issue, but I didn't work on this particular project from the start, so I'm just using the logic that the person before me used.It would be very time-consuming for me to change the way inserting, selecting, updating data etc works.

Tomorrow I will work with the person responsible for database maintenance and hopefully we'll figure this one out.

Thank you all for your replies

[techgnome]

? what the heck?

the database side is done... you have a stored procedure...
Should be as simple as something like this:

        ' Create a new command.
        Using commandToProcess As New System.Data.SqlClient.SqlCommand()

            ' Set up the command.
            With commandToProcess
                .CommandText = "you_sproc_Name_here"
                .CommandType = System.Data.CommandType.StoredProcedure
                .Connection = yourConnectionObject
                .Parameters.AddWithValue("@lParamNumber", iParameter1Value)
                .Parameters.AddWithValue("@cParamChar", cParameterTwo)
                .Parameters.AddWithValue("@bParamBoolean", 1) 'Always return the approved records
                .Parameters.AddWithValue("@sParamString", strUserName)
            End With

            ' Create a new data adapter.
            Using dataAdapter As New System.Data.SqlClient.SqlDataAdapter(commandToProcess)
                Dim loadedData As New myTypedDataSet.MyTypedDataTable  'System.Data.DataTable

                ' Execute the command, and store the result.
                dataAdapter.Fill(loadedData)

                myExistingDataTable.Merge(loadedData, False, System.Data.MissingSchemaAction.Ignore)

            End Using

        End Using

Or, to do updates/inserts

        ' Create a new command.
        Using commandToProcess As New System.Data.SqlClient.SqlCommand()

            ' Set up the command.
            With commandToProcess
                .CommandText = "you_sproc_Name_here"
                .CommandType = System.Data.CommandType.StoredProcedure
                .Connection = yourConnectionObject
                .Parameters.AddWithValue("@lParamNumber", iParameter1Value)
                .Parameters.AddWithValue("@cParamChar", cParameterTwo)
                .Parameters.AddWithValue("@bParamBoolean", 1) 'Always return the approved records
                .Parameters.AddWithValue("@sParamString", strUserName)
            End With

	    commandToProcess.ExecuteNonQuery

        End Using

-tg

[RunsWithScissors]

Also, is there some reason you can't set up the parameters in your SQL code to default to Null? It looks like most of the pain you are experiencing has as much to do with passing in all the single quotes, and tracking that many params.

Seems to me you could get rid of all the conditional statements in your sproc, have your params default to NULL (Or other appropriate value), and then only pass in those that have a value.

Last, for myself only, I prefer to pass in a -1 value instead of NULL for Integer Idenentifiers (such as the BusinessPartnerTypeCode Param). This way, client code reading the record can check for values > 0, and avoid the Null handling from whatever flavor of .NET you are using.

As en example:

ALTER PROCEDURE [dbo].[businessPartnerInsert] 
@businessPartnerCode nvarchar(10) = NULL,
@businessPartnerNameShort nvarchar(20) = NULL,
@businessPartnerName nvarchar(200) = NULL,
@businessPartnerTypeCode int =  -1, -- Or NULL, depending on your preference . . .

 . . . And so on.

Really. It might make life MUCH simpler . . .

But you should ALSO consider the advice TG has given. If you combined THIS approach in the back side, with a sqlCommand and Parameters on the FRONT side, you will find the code a LOT easier to deal with.

Hope that helps!

-rws