Syntax error near ',' - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Syntax error near ','

Author

Topic

DarthDims
Starting Member

2 Posts

Posted - 2011-12-05 : 06:29:27

Hi

I'm using VB.NET 2008 with MS SQL Server I have the following table in the following database:

Database:
USE [master]
GO
/****** Object: Database [knigovodstvo] Script Date: 12/05/2011 12:26:06 ******/
CREATE DATABASE [knigovodstvo] ON PRIMARY
( NAME = N'PRIJAVA', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\PRIJAVA.mdf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'PRIJAVA_log', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\PRIJAVA_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'knigovodstvo', @new_cmptlevel=90
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [knigovodstvo].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [knigovodstvo] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [knigovodstvo] SET ANSI_NULLS OFF
GO
ALTER DATABASE [knigovodstvo] SET ANSI_PADDING OFF
GO
ALTER DATABASE [knigovodstvo] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [knigovodstvo] SET ARITHABORT OFF
GO
ALTER DATABASE [knigovodstvo] SET AUTO_CLOSE ON
GO
ALTER DATABASE [knigovodstvo] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [knigovodstvo] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [knigovodstvo] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [knigovodstvo] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [knigovodstvo] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [knigovodstvo] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [knigovodstvo] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [knigovodstvo] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [knigovodstvo] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [knigovodstvo] SET ENABLE_BROKER
GO
ALTER DATABASE [knigovodstvo] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [knigovodstvo] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [knigovodstvo] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [knigovodstvo] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [knigovodstvo] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [knigovodstvo] SET READ_WRITE
GO
ALTER DATABASE [knigovodstvo] SET RECOVERY SIMPLE
GO
ALTER DATABASE [knigovodstvo] SET MULTI_USER
GO
ALTER DATABASE [knigovodstvo] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [knigovodstvo] SET DB_CHAINING OFF

Table:

USE [knigovodstvo]
GO
/****** Object: Table [dbo].[KOMINTENTI] Script Date: 12/05/2011 12:26:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[KOMINTENTI](
[FIRFIRS] [smallint] NOT NULL,
[KOMKOMIS] [smallint] NOT NULL,
[KOMKOMIN] [char](50) NULL,
[KOMDRZS] [smallint] NULL CONSTRAINT [DF_KOMINTENTI_KOMDRZS] DEFAULT ((0)),
[KOMDRZN] [char](30) NULL,
[KOMMESS] [smallint] NULL CONSTRAINT [DF_KOMINTENTI_KOMMESS] DEFAULT ((0)),
[KOMMESN] [char](30) NULL,
[KOMPOSS] [smallint] NULL CONSTRAINT [DF_KOMINTENTI_KOMPOSS] DEFAULT ((0)),
[KOMPOSN] [char](30) NULL,
[KOMSEL] [char](20) NULL,
[KOMADR] [char](40) NULL,
[KOMZIR1] [char](40) NULL,
[KOMDEV1] [char](40) NULL,
[KOMSWI1] [char](40) NULL,
[KOMBAN1] [char](50) NULL,
[KOMZIR2] [char](40) NULL,
[KOMDEV2] [char](40) NULL,
[KOMSWI2] [char](40) NULL,
[KOMBAN2] [char](50) NULL,
[KOMZIR3] [char](40) NULL,
[KOMDEV3] [char](40) NULL,
[KOMSWI3] [char](40) NULL,
[KOMBAN3] [char](50) NULL,
[KOMDBR] [char](40) NULL,
[KOMMREGB] [char](40) NULL,
[KOMMATF] [smallint] NULL CONSTRAINT [DF_KOMINTENTI_KOMMATF] DEFAULT ((0)),
[KOMDEJS] [smallint] NULL CONSTRAINT [DF_KOMINTENTI_KOMDEJS] DEFAULT ((0)),
[KOMDEJN] [char](30) NULL,
[KOMPOVS1] [char](40) NULL,
[KOMPOZS1] [char](40) NULL,
[KOMFIX1] [char](40) NULL,
[KOMOPERS1] [char](15) NULL,
[KOMOZEMS1] [char](15) NULL,
[KOMPOVS2] [char](40) NULL,
[KOMPOZS2] [char](40) NULL,
[KOMFIX2] [char](40) NULL,
[KOMOPERS2] [char](15) NULL,
[KOMZEMS2] [char](15) NULL,
[KOMPOVF1] [char](40) NULL,
[KOMPOZF1] [char](40) NULL,
[KOMFAX1] [char](40) NULL,
[KOMOPERF1] [char](15) NULL,
[KOMZEMF1] [char](15) NULL,
[KOMPOVM1] [char](40) NULL,
[KOMPOZM1] [char](40) NULL,
[KOMMOB1] [char](40) NULL,
[KOMOPERM1] [char](15) NULL,
[KOMZEMM1] [char](15) NULL,
[KOMEPOS1] [char](40) NULL,
[KOMVEB1] [char](40) NULL,
[KOMLICE1] [char](40) NULL,
[KOMTIP] [smallint] NULL CONSTRAINT [DF_KOMINTENTI_KOMTIP] DEFAULT ((0)),
[KOMVIDF] [smallint] NULL CONSTRAINT [DF_KOMINTENTI_KOMVIDF] DEFAULT ((0)),
[KOMRABAT] [smallint] NULL CONSTRAINT [DF_KOMINTENTI_KOMRABAT] DEFAULT ((0)),
[KOMOCEK] [smallint] NULL CONSTRAINT [DF_KOMINTENTI_KOMOCEK] DEFAULT ((0)),
[KOMVALUT] [smallint] NULL CONSTRAINT [DF_KOMINTENTI_KOMVALUT] DEFAULT ((0)),
[KOMOPIS] [smallint] NULL CONSTRAINT [DF_KOMINTENTI_KOMOPIS] DEFAULT ((0)),
[KOMKOMES] [smallint] NULL CONSTRAINT [DF_KOMINTENTI_KOMKOMES] DEFAULT ((0)),
[KOMKOMEN] [char](40) NULL,
[KOMDATDD] [char](2) NULL,
[KOMDATMM] [char](2) NULL,
[KOMDATGG] [char](4) NULL,
CONSTRAINT [PK_KOMINTENTI_1] PRIMARY KEY CLUSTERED
(
[FIRFIRS] ASC,
[KOMKOMIS] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

And I have a querry to INSERT DATA INTO THE TABLE through VB.NET

QUERY:
strSQL = "INSERT INTO KOMINTENTI (FIRFIRS, KOMKOMIS, KOMKOMIN, " & _
"KOMDRZS, KOMDRZN, KOMMESS, KOMMESN, " & _
"KOMOPSS, KOMOPSN, KOMSEL, KOMADR, " & _
"KOMZIR1, KOMDEV1, KOMSWI1, KOMBAN1, " & _
"KOMZIR2, KOMDEV2, KOMSWI2, KOMBAN2, " & _
"KOMZIR3, KOMDEV3, KOMSWI3, KOMBAN3, " & _
"KOMDBR, KOMREGB, KOMMATF, KOMDEJS, " & _
"KOMDEJN, KOMPOVS1, KOMPOZS1, KOMFIXS1, " & _
"KOMOPERS1, KOMZEMS1, KOMPOVS2, KOMPOZS2, " & _
"KOMFIXS2, KOMOPERS2, KOMZEMS2, KOMPOVF1, " & _
"KOMPOZF1, KOMFAX1, KOMOPERF1, KOMZEMF1, " & _
"KOMPOVM1, KOMPOZM1, KOMMOB1, KOMOPERM1, " & _
"KOMZEMM1, KOMEPOS1, KOMVEB1, KOMLICE1, " & _
"KOMTIP, KOMVIDF, KOMRABAT, KOMOCEK, " & _
"KOMVALUT, KOMOPIS, KOMKOMES, KOMKOMEN, " & _
"KOMDATDD, KOMDATMM, KOMDATGG) " & _
"VALUES (" & Me.TextFIRFIRS.Text & ", " & Me.TextKOMKOMIS.Text & ", 'NOV', " & _
"" & Me.TextKOMDRZS.Text & ", '" & Me.TextKOMDRZN.Text & "', " & Me.TextKOMMESS.Text & ", '" & Me.TextKOMMESN.Text & "', " & _
"" & Me.TextKOMOPSS.Text & ", '" & Me.TextKOMOPSN.Text & "', '" & Me.TextKOMSEL.Text & "', '" & Me.TextKOMADR.Text & "', " & _
"'" & Me.TextKOMZIR1.Text & "', '" & Me.TextKOMDEV1.Text & "', '" & Me.TextKOMSWI1.Text & "', '" & Me.TextKOMBAN1.Text & "', " & _
"'" & Me.TextKOMZIR2.Text & "', '" & Me.TextKOMDEV2.Text & "', '" & Me.TextKOMSWI2.Text & "', '" & Me.TextKOMBAN2.Text & "', " & _
"'" & Me.TextKOMZIR3.Text & "', '" & Me.TextKOMDEV3.Text & "', '" & Me.TextKOMSWI3.Text & "', '" & Me.TextKOMBAN3.Text & "', " & _
"'" & Me.TextKOMDBR.Text & "', '" & Me.TextKOMREGB.Text & "', " & Me.TextKOMMATF.Text & ", " & Me.TextKOMDEJS.Text & ", " & _
"'" & Me.TextKOMDEJN.Text & "', '" & Me.TextKOMPOVS1.Text & "', '" & Me.TextKOMPOZS1.Text & "', '" & Me.TextKOMFIXS1.Text & "', " & _
"'" & Me.TextKOMOPERS1.Text & "', '" & Me.TextKOMZEMS1.Text & "', '" & Me.TextKOMPOVS2.Text & "', '" & Me.TextKOMPOZS2.Text & "', " & _
"'" & Me.TextKOMFIXS2.Text & "', '" & Me.TextKOMOPERS2.Text & "', '" & Me.TextKOMZEMS2.Text & "', '" & Me.TextKOMPOVF1.Text & "', " & _
"'" & Me.TextKOMPOZF1.Text & "', '" & Me.TextKOMFAX1.Text & "', '" & Me.TextKOMOPERF1.Text & "', '" & Me.TextKOMPOVM1.Text & "', " & _
"'" & Me.TextKOMPOVM1.Text & "', '" & Me.TextKOMPOZM1.Text & "', '" & Me.TextKOMMOB1.Text & "', '" & Me.TextKOMOPERM1.Text & "', " & _
"'" & Me.TextKOMZEMM1.Text & "', '" & Me.TextKOMEPOS1.Text & "', '" & Me.TextKOMVEB1.Text & "', '" & Me.TextKOMLICE1.Text & "', " & _
"" & Me.TextKOMTIP.Text & ", " & Me.TextKOMVIDF.Text & ", " & Me.TextKOMRABAT.Text & ", " & Me.TextKOMOCEK.Text & ", " & _
"" & Me.TextKOMVALUT.Text & ", " & Me.TextKOMOPIS.Text & ", '" & Me.TextKOMKOMES.Text & "', '" & Me.TextKOMKOMEN.Text & "', " & _
"'" & Me.TextKOMDATDD.Text & "', '" & Me.TextKOMDATMM.Text & "', '" & Me.TextKOMDATGG.Text & "')"

When any of the fields that suppose to hold smallint values is empty in the Form I get an error "Syntax error near ','

How to get pass this?

Any ideas will be appreciated

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-12-05 : 06:33:25

Probably because you are sneding ,, whereas you need to send ,0,.
You need a conditional to put in a value.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-05 : 06:34:00

check for empty values in each of controls like Me.TextKOMEPOS1.Text etc and in that case make the value as "NULL"and pass it to insert

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DarthDims
Starting Member

2 Posts

Posted - 2011-12-05 : 08:11:35

Is there a way to avoid checking the form controls.

In general I would like to know if this is going to be the case for every table or this is just a specific case.

My table fields allow nulls, and there is no problem with the chars, they do accept nulls

And I know the error is generated because of the , , where it should be ,0, but it should pass with a null value like the strings do, at least it is what I think

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-12-05 : 09:48:30

Then you wnat the text null. There's no way round checking each one but you can cretae a function to do it for you.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources