MS SQL Server :: Case Statement Error In An Insert Statement

If Or Case In An Insert Statement

Apr 18, 2006

when inserting into a temp table, if im creating the table there an then with select into statement, can i use if or case statements to decide on column names.

eg select if bla = 0 then call col a else call col b

into #temptable

View 1 Replies View Related

Using CASE With INSERT,UPDATE Statement

Mar 6, 2008

Is it possible to use CASE statement with INSERT /UPDATE statement?this is what i am trying to do
i have a table like this
Field1 Field2 Field3 FieldType1 FieldType2 FieldType3
1-when there is no data for a given combination of Field2 and Field3,i need to do "insert".i.e the very first time and only time
2-Second time,when there is already a row created for a given combination of Field2 and Field3,i would do the update onwards from there on.
At a time value can be present for only one of the FieldType1,FieldType2,FieldType3) for insert or update
I am passing a parameter to the stored procedure,which needs to be evaluated and wud determine,which field out of (FieldType1,FieldType2,FieldType3)has a value for insert or update .this is what i am trying to do in a stored procedure
CREATE PROCEDURE dbo.StoredProcedure
( @intField1 int, @intField2 int, @intField3 int, @intFieldValue int , @evalFieldName varchar(4) )So i am trying something like
CaseWHEN @evalFieldName ="Fld1" THENINSERT INTO TABLE1 (Field2,Field3,fieldType1,FieldType2,FieldType3)values (@intField1,@intField2,@intField3,@intFieldValue,cast(null as int) fld2 ,cast(null as int) fld3)
CaseWHEN @evalFieldName ="Fld2" THENINSERT INTO TABLE1 (Field2,Field3,fieldType1,FieldType2,FieldType3)values (@intField1,@intField2,@intField3,cast(null as int) fld1 ,@intFieldValue,cast(null as int) fld3)
CaseWHEN @evalFieldName ="Fld3" THENINSERT INTO TABLE1 (Field2,Field3,fieldType1,FieldType2,FieldType3)values (@intField1,@intField2,@intField3,cast(null as int) fld1 ,cast(null as int) fld2,@intFieldValue)
END
similar trend needs to be followed for UPDATE as well..obiviousely its not working,gives me synatax error at case,when,then everywher.so can someone suggest me the alternative way?..i am trying to avoid writing stored procedure to insert/update for each individual fields..thanks a lot

View 8 Replies View Related

Using IsNumeric Within A Case Statement (within An Insert)

Mar 25, 2003

Hi - Please excuse me if this is really simple, but I'm fairly new to this lark.

My (made up) code is below... I'd be grateful for any pointers.

insert into [tblInvoices]
(full_period,
supplier_no,
account_code,
tran_amount,
function)
select
full_period,
supplier_no,
account_code,
tran_amount
case
when substring(account_code,1,2) = 'FY' then '-'
when isNumeric(account_code) then left(account_code, 2)
when not isNumeric(substring(account_code,1,2)) then left(account_code, 1)
else 'oops'
end as function,
from
tblLoadMSV900_i
end

Is this even close?

I'm using a stored proc to insert the data from tblLoadMSV900_i into tblInvoices and at the same time insert some data into the function field.

In plain english I want make sure that:
If the first 2 chars of account_code are 'FY' then function='-',
If the first char of account_code is numeric then function=left(account_code, 2),
If the the first char is not numeric (and if first two chars are not 'FY' i.e. first char could be 'F') then function=left(account_code, 1)

And there's plenty more where this came from! But if I can crack this with your help then I should have a better idea about the rest of the proc.

Thanks
Sara

View 2 Replies View Related

Can Insert Statement Works In Case When Function

Nov 5, 2007

Hi

Im beginer in sql,Please guide

can insert statement works fine in case when function

for example

case when condition1=true then (first insert statement based on some condition) when condition2=true then (second insert statement based on some other condition)
end

View 5 Replies View Related

SQL Server 2012 :: Update Statement With CASE Statement?

Aug 13, 2014

i was tasked to created an UPDATE statement for 6 tables , i would like to update 4 columns within the 6 tables , they all contains the same column names. the table gets its information from the source table, however the data that is transferd to the 6 tables are sometimes incorrect , i need to write a UPDATE statement that will automatically correct the data. the Update statement should also contact a where clause

the columns are [No] , [Salesperson Code], [Country Code] and [Country Name]

i was thinking of doing

Update [tablename]
SET [No] =
CASE
WHEN [No] ='AF01' THEN 'Country Code' = 'ZA7' AND 'Country Name' = 'South Africa'
ELSE 'Null'
END

What is the best way to script this

View 1 Replies View Related

How To Write Select Statement Inside CASE Statement ?

Jul 4, 2006

Hello friends,
I want to use select statement in a CASE inside procedure.
can I do it? of yes then how can i do it ?

following part of the procedure clears my requirement.

SELECT E.EmployeeID,
CASE E.EmployeeType
WHEN 1 THEN
select * from Tbl1
WHEN 2 THEN
select * from Tbl2
WHEN 3 THEN
select * from Tbl3
END
FROM EMPLOYEE E

can any one help me in this?
please give me a sample query.

Thanks and Regards,
Kiran Suthar

View 7 Replies View Related

Transact SQL :: Update Statement In Select Case Statement

May 5, 2015

I am attempting to run update statements within a SELECT CASE statement.

Select case x.field
WHEN 'XXX' THEN
UPDATE TABLE1
   SET TABLE1.FIELD2 = 1
ELSE
   UPDATE TABLE2
   SET TABLE2.FIELD1 = 2
END
FROM OuterTable x

I get incorrect syntax near the keyword 'update'.

View 7 Replies View Related

How To Show Records Using Sql Case Statement Or If Else Statement

Feb 20, 2008

i want to display records as per if else condition in ms sql query,for this i have used tables ,queries as follows

as per data in MS Sql

my tables are as follows
1)material
fields are -- material_id,project_type,project_id,qty, --

2)AB_Corporate_project
fields are-- ab_crp_id,custname,contract_no,field_no

3)Other_project
fields are -- other_proj_id,other_custname,po

for ex :
vales in table's are
AB_Corporate_project
=====================
ab_crp_id custname contract_no field_no
1 abc 234 66
2 xyz 33 20

Other_project
============
other_proj_id other_custname po
1 xxcx 111
2 dsd 222

material
=========
material_id project_type project_id qty
1 AB Corporate 1 3
2 Other Project 2 7

i have taken AB Corporate for AB_Corporate_project ,Other Project for Other_project

sample query i write :--

select m.material_id ,m.project_type,m.project_id,m.qty,ab.ab_crp_id,
ab.custname ,op.other_proj_id,op.other_custname,op. po
case if m.project_type = 'AB Corporate' then
select * from AB_Corporate_project where ab.ab_crp_id = m.project_id
else if m.project_type = 'Other Project' then
select * from Other_project where op.other_proj_id=m.project_id
end
from material m,AB_Corporate_project ab,Other_project op

but this query not work,also it gives errors

i want sql query to show data as follows

material_id project_type project_id custname other_custname qty
1 AB Corporate 1 abc -- 3
2 Other Project 2 -- dsd 7

so plz help me how can i write sql query for to show the output
plz send a sql query

View 8 Replies View Related

Error While Trying To Execute An SP In My CASE STATEMENT

Jan 22, 2007

Hi all,
How do i execute a stored procedure in the THEN CLAUSE of my CASE STATEMENT? Av been getting errors since.

Here is my code:

Alter PROCEDURE sp_getTxn (
@m1 int = Null,
@txn int = Null,
@p2 int = Null,
@amt int = Null,
@pAccountno varchar(50) = 'Null',
@DAcct int = Null,
@Balance Decimal(19,4) = NULL OUTPUT,
@pBalance Decimal(19,4) = NULL OUTPUT,
@RowsReturned smallint = NULL OUTPUT )
AS
SET NOCOUNT ON

select CASE
WHEN @m1 = 200 THEN case
when @txn = 00 then ('exec dbo.CustOrderHist (@CrAcct int)')

when @txn = 01 then ('exec dbo.Sp_withdrawal')

when @txn = 31 then exec dbo.CheckBalance(@pAccountno varchar(50), @pBalance Decimal(19,4) OUTPUT)

when @txn = 38 then ('exec dbo.Sp_StatementOfAcct')
END
END
WHEN @m1 = 420 THEN case
when @txnType = 00 then ('exec dbo.Sp_reversal')

when @txnType = 01 then ('exec dbo.Sp_reversal2')

when @txnType = 31 then ('exec dbo.Sp_reversal3')
END
END

SET @Balance = @pBalance
Print @Balance

Or is there an alternative to the above CASE statement that is easier and faster?

Thanks

View 2 Replies View Related

Error In Image Field When Using CASE Statement

Dec 7, 2004

I've this Stored procedure on a SQLserver 2000 SP3:

SELECT *,CASE immagine WHEN NULL THEN 0 ELSE 1 END AS hasImage
FROM Squadre WHERE squadra = @squadra

this is a flag that returns if the image field is present or not..
i've a lot of this type of stored procedures.. but this one returns me an error..

---------------------------
Microsoft SQL-DMO (ODBC SQLState: 42000)
---------------------------
Errore 306: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
---------------------------
OK
---------------------------

An i can't save.. why?
reme,ber that in the same Db there's other Stored like this.. the same syntax and the same field or table.. can anyone help me??

View 2 Replies View Related

Error Using Case Expression In Order By Statement

Feb 22, 2008

I have been trying to get the following Selects to work using a case expression in the order by section.

I know I can easily separate out the two statements but I want to do a select using the case statement below ;
however, I keep getting error 16 --"Order by items must appear in the select list if the statement contains a union.

If remove the case statement and put order by "internalID desc" I receive no errors. Moreover, when I take out the union statement and execute the two select statements with each including the case expression it runs as planned.

Can anyone tell what the problem is here? I have combed the web looking for an answer, but it seems that the statement is valid.

Thanks
J
declare @date
set @date = '2001'

select internalID from section_data_v3

union

select internalID from section_data_v4

order by
case when (@date = '2001') then internalID end desc

View 5 Replies View Related

CASE Statement Error: Cannot Convert A Char Value To Money

Nov 6, 2007

I am trying to write a Procedure in SQL 2005 that retreives a list of rows from the database. I pass to the procedure sorting parameters. This procedure works fine for all of the fields that I enter in the Order By, except for one.
The field that does not work is a varchar(500) field (named Description).
I am not sure what the problem is.
The error that I am getting is:

Msg 235, Level 16, State 0, Line 13

Cannot convert a char value to money. The char value has incorrect syntax.

Attached is the T-SQL Code. The declare and set statements in the beginning are for informational purposes.
Any help is appreciated.
------------------------------------------------------------------------------------------------------
declare @Match varchar(75)

declare @NumRows int

declare @StartPos int

declare @OrderBy varchar(25)

declare @OrderDir varchar(4)

set @Match = 'dog% AND bark%'

set @NumRows = 25

set @StartPos = 100

set @OrderBy = 'Description'

set @OrderDir = 'ASC'

WITH catitems AS

(

SELECT ci.ID, ci.SupplierCode, ci.Description, ci.AUDIO_LINK, ci.SoundLength, ci.HighCost, ci.Channels,

ROW_NUMBER() OVER (ORDER BY

CASE

WHEN @OrderBy='ID' AND @OrderDir='ASC'

THEN CAST(ci.ID AS int)

WHEN @OrderBy='SupplierCode' AND @OrderDir='ASC'

THEN CAST(ci.SupplierCode As int)

WHEN @OrderBy='Description' AND @OrderDir='ASC'

THEN CAST(ci.[Description] As varchar(500))

WHEN @OrderBy='HighCost' AND @OrderDir='ASC'

THEN CAST(ci.HighCost AS money)

WHEN @OrderBy='Channels' AND @OrderDir='ASC'

THEN CAST(ci.Channels AS smallint)

END ASC,

CASE

WHEN @OrderBy='ID' AND @OrderDir='DESC'

THEN CAST(ci.ID AS int)

WHEN @OrderBy='SupplierCode' AND @OrderDir='DESC'

THEN CAST(ci.SupplierCode As int)

WHEN @OrderBy='Description' AND @OrderDir='DESC'

THEN CAST(ci.[Description] As varchar(500))

WHEN @OrderBy='HighCost' AND @OrderDir='DESC'

THEN CAST(ci.HighCost AS money)

WHEN @OrderBy='Channels' AND @OrderDir='DESC'

THEN CAST(ci.Channels AS smallint)

END DESC) As RowNo

FROM TableName As ci

WHERE CONTAINS (ci.Keywords, @Match) AND ShowOnWeb=1

)

SELECT catitems.ID,

catitems.SupplierCode,

catitems.Description,

catitems.AUDIO_LINK,

catitems.HighCost,

catitems.Channels

FROM catitems

WHERE RowNo BETWEEN @StartPos AND @StartPos + @NumRows -1
------------------------------------------------------------------------------------------------------

Thanks,

Aric

View 1 Replies View Related

DB Engine :: Can't Use The MERGE Statement / How To Design WHERE Condition For Insert Statement

Nov 5, 2015

I've have a need with SQL Server 2005 (so I've no MERGE statement), I have to merge 2 tables, the target table has 10 fields, the first 4 are the clustered index and primary key, the source table has the same fields and index.Since I can't use the MERGE statement (I'm in SQL 2005) I have to make a double step operation, and INSERT and an UPDATE, I can't figure how to design the WHERE condition for the insert statement.

View 2 Replies View Related

Cannot INSERT Data To 3 Tables Linked With Relationship (INSERT Statement Conflicted With The FOREIGN KEY Constraint Error)

Apr 9, 2007

Hello
I have a problem with setting relations properly when inserting data using adonet. Already have searched for a solutions, still not finding a mistake...
Here's the sql management studio diagram :

and here goes the code1 DataSet ds = new DataSet();
2
3 SqlDataAdapter myCommand1 = new SqlDataAdapter("select * from SurveyTemplate", myConnection);
4 SqlCommandBuilder cb = new SqlCommandBuilder(myCommand1);
5 myCommand1.FillSchema(ds, SchemaType.Source);
6 DataTable pTable = ds.Tables["Table"];
7 pTable.TableName = "SurveyTemplate";
8 myCommand1.InsertCommand = cb.GetInsertCommand();
9 myCommand1.InsertCommand.Connection = myConnection;
10
11 SqlDataAdapter myCommand2 = new SqlDataAdapter("select * from Question", myConnection);
12 cb = new SqlCommandBuilder(myCommand2);
13 myCommand2.FillSchema(ds, SchemaType.Source);
14 pTable = ds.Tables["Table"];
15 pTable.TableName = "Question";
16 myCommand2.InsertCommand = cb.GetInsertCommand();
17 myCommand2.InsertCommand.Connection = myConnection;
18
19 SqlDataAdapter myCommand3 = new SqlDataAdapter("select * from Possible_Answer", myConnection);
20 cb = new SqlCommandBuilder(myCommand3);
21 myCommand3.FillSchema(ds, SchemaType.Source);
22 pTable = ds.Tables["Table"];
23 pTable.TableName = "Possible_Answer";
24 myCommand3.InsertCommand = cb.GetInsertCommand();
25 myCommand3.InsertCommand.Connection = myConnection;
26
27 ds.Relations.Add(new DataRelation("FK_Question_SurveyTemplate", ds.Tables["SurveyTemplate"].Columns["id"], ds.Tables["Question"].Columns["surveyTemplateID"]));
28 ds.Relations.Add(new DataRelation("FK_Possible_Answer_Question", ds.Tables["Question"].Columns["id"], ds.Tables["Possible_Answer"].Columns["questionID"]));
29
30 DataRow dr = ds.Tables["SurveyTemplate"].NewRow();
31 dr["name"] = o[0];
32 dr["description"] = o[1];
33 dr["active"] = 1;
34 ds.Tables["SurveyTemplate"].Rows.Add(dr);
35
36 DataRow dr1 = ds.Tables["Question"].NewRow();
37 dr1["questionIndex"] = 1;
38 dr1["questionContent"] = "q1";
39 dr1.SetParentRow(dr);
40 ds.Tables["Question"].Rows.Add(dr1);
41
42 DataRow dr2 = ds.Tables["Possible_Answer"].NewRow();
43 dr2["answerIndex"] = 1;
44 dr2["answerContent"] = "a11";
45 dr2.SetParentRow(dr1);
46 ds.Tables["Possible_Answer"].Rows.Add(dr2);
47
48 dr1 = ds.Tables["Question"].NewRow();
49 dr1["questionIndex"] = 2;
50 dr1["questionContent"] = "q2";
51 dr1.SetParentRow(dr);
52 ds.Tables["Question"].Rows.Add(dr1);
53
54 dr2 = ds.Tables["Possible_Answer"].NewRow();
55 dr2["answerIndex"] = 1;
56 dr2["answerContent"] = "a21";
57 dr2.SetParentRow(dr1);
58 ds.Tables["Possible_Answer"].Rows.Add(dr2);
59
60 dr2 = ds.Tables["Possible_Answer"].NewRow();
61 dr2["answerIndex"] = 2;
62 dr2["answerContent"] = "a22";
63 dr2.SetParentRow(dr1);
64 ds.Tables["Possible_Answer"].Rows.Add(dr2);
65
66 myCommand1.Update(ds,"SurveyTemplate");
67 myCommand2.Update(ds, "Question");
68 myCommand3.Update(ds, "Possible_Answer");
69 ds.AcceptChanges();
70

and that causes (at line 67):"The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_Question_SurveyTemplate". The conflict occurred in database
"ankietyzacja", table "dbo.SurveyTemplate", column
'id'.
The statement has been terminated.
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
at AnkietyzacjaWebService.Service1.createSurveyTemplate(Object[] o) in J:\PL\PAI\AnkietyzacjaWebService\AnkietyzacjaWebServicece\Service1.asmx.cs:line 397"

Could You please tell me what am I missing here ?
Thanks a lot.

View 5 Replies View Related

How To Use Select Statement Inside Insert Statement

Oct 20, 2014

In the below code i want to use select statement for getting customer

address1,customeraddress2,customerphone,customercity,customerstate,customercountry,customerfirstname,customerlastname

from customer table.Rest of the things will be as it is in the following code.How do i do this?

INSERT INTO EMImportListing ("
sql += " CustId,Title,Description,JobCity,JobState,JobPostalCode,JobCountry,URL,Requirements, "
sql += " IsDraft,IsFeatured,IsApproved,"
sql += " Email,OrgName,customerAddress1,customerAddress2,customerCity,customerState,customerPostalCode,

[code]....

View 1 Replies View Related

Insert Statement Error

Apr 8, 2005

Hi All,

I am trying to run an insert statement and getting an error.

Insert statement:

insert into conv_owner (name,street_num,
street_direction,street_name,street_type,street_un it,
address2,city,state,city_state,zip,wphone,inservic e,
db_name,table_name)
select "Facility's Owner",null,null,null,null,null,null,null,
null,null,null,"Owner's Telephone Number",inservice,
'BKFoodProtection.mdb','FP_Master'
from fp_master

Error message:
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

Any idea why? Thanks.

View 6 Replies View Related

Error In Insert Statement ........Help Out

May 13, 2008

Hi
All as i have a class where i wrote a procedure for insert statement and i passed the value from the form and previously it was fine and now its giving the following error

" The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.The statement has been terminated."

And
code is as follows

Code Snippet

Public Sub Purchase_Header(ByVal BillNo As VariantType, ByVal BillDate As Date, ByVal GRNNO As VariantType, ByVal GRNdate As Date, ByVal TOP As Integer, ByVal Supplier As Integer, ByVal TotalPurchasevalue As Double, ByVal TotalProductValue As Double, ByVal BillPaid As Date, ByVal BillDue As Date, ByVal NoOfSBills As Integer, ByVal noofbreceived As Integer, ByVal billstatus As Integer, ByVal paymentstatus As Integer, ByVal appstatus As Integer, ByVal recitstatus As Boolean, ByVal sbillstatus As Boolean, ByVal ccindicator As String)

connection()
myCommand = New SqlCommand("INSERT INTO Purchase_Header(PH_Voucher_Date,PH_Bill_No,PH_Bill_Date,PH_GRN_No,PH_GRN_Date,PH_Type_Of_Purchase_Id,PH_Supplier_Id,PH_Total_Purchase_Value,PH_Total_Product_Value,PH_Bill_Paid_On,PH_Bill_Due_On,PH_No_Of_Sub_Bills,PH_No_Of_Sub_Bills_Received,PH_Bill_Status,PH_Payment_Status,PH_Apportionment_Status,PH_Goods_Receipt_Status,PH_Sub_Bill_Receipt_Status,PH_Cash_Credit_Indicator,PH_Total_Tax_Paid) VALUES('" & System.DateTime.Today & "'," & BillNo & ",'" & BillDate & "'," & GRNNO & ",'" & GRNdate & "'," & TOP & "," & Supplier & "," & TotalPurchasevalue & "," & TotalProductValue & ",'" & BillPaid & "','" & BillDue & "'," & NoOfSBills & "," & noofbreceived & "," & billstatus & "," & paymentstatus & "," & appstatus & ",'" & recitstatus & "','" & sbillstatus & "' ,'" & ccindicator & "',0)", myConnection)
myCommand.ExecuteReader()

MsgBox("Data saved successfully")
myConnection.Close()
and table structure is as follows and for date types i am passing them through date variables from textbox

INSERT INTO [MoneyBee].[dbo].[Purchase_Header]
([PH_Voucher_Date]
,[PH_Bill_No]
,[PH_Bill_Date]
,[PH_GRN_No]
,[PH_GRN_Date]
,[PH_Type_Of_Purchase_Id]
,[PH_Supplier_Id]
,[PH_Total_Purchase_Value]
,[PH_Total_Product_Value]
,[PH_Bill_Paid_On]
,[PH_Bill_Due_On]
,[PH_No_Of_Sub_Bills]
,[PH_No_Of_Sub_Bills_Received]
,[PH_Bill_Status]
,[PH_Payment_Status]
,[PH_Apportionment_Status]
,[PH_Goods_Receipt_Status]
,[PH_Sub_Bill_Receipt_Status]
,[PH_Cash_Credit_Indicator]
,[PH_Total_Tax_Paid])
VALUES
(<PH_Voucher_Date, datetime,>
,<PH_Bill_No, varchar(15),>
,<PH_Bill_Date, datetime,>
,<PH_GRN_No, numeric,>
,<PH_GRN_Date, datetime,>
,<PH_Type_Of_Purchase_Id, numeric,>
,<PH_Supplier_Id, numeric,>
,<PH_Total_Purchase_Value, numeric,>
,<PH_Total_Product_Value, numeric,>
,<PH_Bill_Paid_On, datetime,>
,<PH_Bill_Due_On, datetime,>
,<PH_No_Of_Sub_Bills, numeric,>
,<PH_No_Of_Sub_Bills_Received, numeric,>
,<PH_Bill_Status, numeric,>
,<PH_Payment_Status, numeric,>
,<PH_Apportionment_Status, numeric,>
,<PH_Goods_Receipt_Status, bit,>
,<PH_Sub_Bill_Receipt_Status, bit,>
,<PH_Cash_Credit_Indicator, varchar(6),>
,<PH_Total_Tax_Paid, numeric,>)

Thanks
Avinash

View 5 Replies View Related

Error:: The Insert Statement ...

Apr 26, 2008

hi

i'm having a problem with my project we need to make for school with asp.net & c#. the problem is i'm a newbie with c#.
the project we need to make is a survey. so the problem is my first question of the survey works fine when i push the go to next it enters the data nice into my sql db and my second question loads, then I enter the answer for my second question i press go to next question and i get this error: " The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Antwoorden_Gebruikers". The conflict occurred in database "GIP_enquete", table "dbo.Gebruikers", column 'Gebruiker_ID'. " I don't know how i can fix this cause im to newbie to understand whats wrong.

here is a view of my code:

Code Snippet

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using enqueteTableAdapters;

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
int Vraag_ID = Convert.ToInt16(Request.QueryString["vraagid"]);
VragenTableAdapter VraagAdapter = new VragenTableAdapter();
lblVraag.Text = Convert.ToString(VraagAdapter.GeefVraag(Vraag_ID));

if (Vraag_ID == 1)
{
pnlVraag1.Visible = true;
}

if (Vraag_ID == 2)
{
pnlVraag2.Visible = true;
}

}
protected void btnVraag1_Click(object sender, EventArgs e)
{
//make a new user
GebruikersTableAdapter GebruikerAdapter = new GebruikersTableAdapter();
DateTime Moment = System.DateTime.Now;
GebruikerAdapter.GebruikerToevoegen(Moment);
int GebruikerID = Convert.ToInt16(GebruikerAdapter.GeefGebruikerID(Moment));
this.ViewState.Add("gebruiker_id", GebruikerID);

//send answer
AntwoordenTableAdapter AntwoordAdapter = new AntwoordenTableAdapter();
AntwoordAdapter.AntwoordIngeven(1, GebruikerID, txtVraag1.Text, null);

//go to 2nd question
Response.Redirect("Default.aspx?vraagid=2", true);
}

protected void btnVraag2_Click(object sender, EventArgs e)
{
//get back the user from question 1
int GebruikerID = Convert.ToInt16(this.ViewState["gebruiker_id"]);

//send answer
AntwoordenTableAdapter AntwoordAdapter = new AntwoordenTableAdapter();
AntwoordAdapter.AntwoordIngeven(2, GebruikerID, txtVraag2.Text, null);

when i run debug it allways stops here

//go too 3th question
Response.Redirect("Default.aspx?vraagid=3", true);
}
}

can somebody plz help me ? =(

ps: sorry for my bad english

View 3 Replies View Related

Syntax Error On Insert Statement

Jan 11, 2007

Ok, the following four lines are four lines of code that I'm running, I'll post the code and then explain my issue:
        sqlCommand = New SQLCommand("INSERT INTO Bulk (Bulk_Run, Bulk_Totes, Bulk_Drums, Bulk_Boxes, Bulk_Bags, Bulk_Bins, Bulk_Crates) VALUES (" & RunList(x,0) & ", " & Totes & ", " & Drums & ", " & Boxes & ", " & Bags & ", " & Bins & ", " & Crates & ")", Connection)        sqlCommand.ExecuteNonQuery()        sqlCommand = New SQLCommand("INSERT INTO Presort (Presort_Run, Presort_Totes, Presort_Drums, Presort_Boxes, Presort_Bags, Presort_Bins, Presort_Crates) VALUES (" & RunList(x,0) & ", " & Totes & ", " & Drums & ", " & Boxes & ", " & Bags & ", " & Bins & ", " & Crates & ")", Connection)        sqlCommand.ExecuteNonQuery()
The two tables (Bulk & Presort) are <b>exactly</b> the same. This includes columns, primary keys, IDs, and even permissions. If I run the last two liens (the INSERT INTO Presort) then it works fine without error. But whenever I run the first two lines (the INSERT INTO Bulk) I get the following error:
Incorrect syntax near the keyword 'Bulk'.
Anyone have any ideas, thanks

View 2 Replies View Related

SQL Insert Statement Error That I Cannot Figure Out

Dec 11, 2004

I am receiving the following error when trying to insert values from textboxes on a registration form:

Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: BC30205: End of statement expected.

Source Error:

Line 19: Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Line 20:
Line 21: Dim queryString As String = "INSERT INTO [UserID_db] ([Code], [UserID], [Password], [Email1], [Name_First], [Name_Last], [Admin_level]) VALUES ('" & txtUserID.Text"', '" & txtUserID.Text"', '" & txtPassword.Text"', '" & txtEmail1.Text"', '" & txtFirstName"', '" & txtLastName.Text"', '" & txtAdminLevel.Text"')"
Line 22: Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
Line 23: dbCommand.CommandText = queryString

My code is below. Can anyone help me figure out what is wrong with my code?

<%@ Page Language="VB" Debug="true" %>
<%@ Register TagPrefix="wmx" Namespace="Microsoft.Matrix.Framework.Web.UI" Assembly="Microsoft.Matrix.Framework, Version=0.6.0.0, Culture=neutral, PublicKeyToken=6f763c9966660626" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.OleDb" %>
<script runat="server">

Sub btnSubmit_Click(sender As Object, e As EventArgs)

Dim UCde as string = txtUserID.Text
Dim UserID as string = txtUserID.Text
Dim Password as string = txtPassword.Text
Dim Email1 as string = txtEmail.Text
Dim FirstName as string = txtFirstName.Text
Dim LastName as string = txtLastName.Text
Dim AdminLevel as string = dropAccountType.SelectedItem.Value

Dim connectionString As String = "server='(local)'; trusted_connection=true; database='master'"
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)

Dim queryString As String = "INSERT INTO [UserID_db] ([Code], [UserID], [Password], [Email1], [Name_First], [Name_Last], [Admin_level]) VALUES ('" & txtUserID.Text"', '" & txtUserID.Text"', '" & txtPassword.Text"', '" & txtEmail1.Text"', '" & txtFirstName"', '" & txtLastName.Text"', '" & txtAdminLevel.Text"')"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim rowsAffected As Integer = 0
dbConnection.Open()
dbCommand.ExecuteNonQuery()
dbConnection.Close()
End Sub

Thanks for your help.

Chris

View 5 Replies View Related

Syntax Error On INSERT Statement

Dec 16, 2005

Here is my insert statement: StringBuilder sb = new StringBuilder(); sb.Append("INSERT INTO patients_import_test "); sb.Append("(Referral_Number,Referral_Date,FullName,Patient_ien,DOB,FMP,SSN_LastFour,Race_Id,PCM,Age) "); sb.Append("VALUES(@rnum,@rdate,@fname,@patid,@birthDate,@fmp,@ssan,@race,@pcm,@age) "); sb.Append("WHERE Referral_Number NOT IN ( SELECT Referral_Number FROM patients_import_test )");I'm getting an "Incorrect syntax near the keyword 'WHERE'".If I remove the WHERE clause the INSERT statement work fine.

View 3 Replies View Related

Error In Insert Into Statement Access

Jan 14, 2013

Using Access 2010 and SQL Server 2012..i added a database through SSMS and added a table named tblEmployee (dbo.tblEmployee)

The table has 3 fields
LName (nvarchar(50), null)
FName (nvarchar(50), null)
Code (nvarchar(50), null)

The access table has 3 fields
FName, text
LName, text
Code, text

I found a code snippet here to insert from the Access table to the SQL table.UtterAccess Discussion Forums > Appending Access table to SQL Server table (and vice-versa) usin...The code is generating this error

Run-time error '3134':
Syntax error in INSERT INTO statement

Code:
Option Compare Database
Sub AppToSQL()
Dim strODBCConnect As String
Dim strSQL As String
'Code from:

[code]...

View 5 Replies View Related

Insert Into Statement Syntax Error

May 30, 2006

Hi,

I have a problem as shown below

Server Error in '/ys(Do Not Remove!!!)' Application.

Syntax error in INSERT INTO statement.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement.

Source Error:

Line 8: <Script runat="server">
Line 9: Private Sub InsertAuthorized(ByVal Source As Object, ByVal e As EventArgs)
Line 10: SqlDataSource1.Insert()
Line 11: End Sub ' InsertAuthorized
Line 12: </Script>
Source File: C:Documents and SettingsDream_AchieverDesktopys(Do Not Remove!!!)Authorizing.aspx Line: 10

Stack Trace:

[OleDbException (0x80040e14): Syntax error in INSERT INTO statement.]
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +177
System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +194
System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +56
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +105
System.Data.OleDb.OleDbCommand.ExecuteNonQuery() +88
System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +392
System.Web.UI.WebControls.SqlDataSourceView.ExecuteInsert(IDictionary values) +410
System.Web.UI.WebControls.SqlDataSource.Insert() +13
ASP.authorizing_aspx.InsertAuthorized(Object Source, EventArgs e) in C:Documents and SettingsDream_AchieverDesktopys(Do Not Remove!!!)Authorizing.aspx:10
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +75
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +97
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4919

And part of my program is as shown

<Script runat="server">

Private Sub InsertAuthorized(ByVal Source As Object, ByVal e As EventArgs)

SqlDataSource1.Insert()

End Sub ' InsertAuthorized

</Script>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DIP1ConnectionString %>" ProviderName="<%$ ConnectionStrings:DIP1ConnectionString.ProviderName %>"

InsertCommand="Insert Into Authorize (Army ID,Tag No,Vehicle ID,Vehicle Type,Prescribed Route,Start Time, End Time) VALUES (@ArmyID, @TagNo, @VehicleID, @VehicleType, @PrescribedRoute, @StartTime, @EndTime)">

<insertparameters>

<asp:formparameter name="ArmyID" formfield="ArmyID" />

<asp:formparameter name="TagNo" formfield="TagNo" />

<asp:formparameter name="VehicleID" formfield="VehicleID" />

<asp:formparameter name="VehicleType" formfield="VehicleType" />

<asp:formparameter name="PrescribedRoute" formfield="PrescribedRoute" />

<asp:formparameter name="StartTime" formfield="StartTime" />

<asp:formparameter name="EndTime" formfield="EndTime" />

</insertparameters>

</asp:SqlDataSource>

Anybody can help? thanks

View 1 Replies View Related

Syntax Error In INSERT INTO Statement.

Apr 7, 2008

Public DBString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Q:VoicenetRTS FolderRTS ChargesAccountscosting.mdb"
Private Sub Button13_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button13.Click
Dim connstring As New OleDbConnection(DBString)
connstring.Open()
Dim searchstring As String = "SELECT * FROM Costings1"
Dim da As New OleDbDataAdapter(searchstring, connstring)
Dim DS As New DataSet()
Dim dt As DataTable = DS.Tables("Costings1")
da.FillSchema(DS, SchemaType.Source, "Costings1")
da.Fill(DS, "Costings1")
Dim cb As New OleDbCommandBuilder(da)
da.InsertCommand = cb.GetInsertCommand
da.UpdateCommand = cb.GetUpdateCommand
Dim dr As DataRow = DS.Tables("Costings1").NewRow
dr("Key") = TextBox1.Text
dr("CODE") = TextBox2.Text
dr("Element") = TextBox3.Text
etc...
DS.Tables("Costings1").Rows.Add(dr)
da.Update(DS, "Costings1") <<<<<<<<<<<Syntax error in INSERT INTO statement.

There are no spaces in the field names.

View 9 Replies View Related

Error:: The Insert Statement Conflicted With ..

Apr 26, 2008

hi

i'm having a problem with my project we need to make for school with asp.net & c#. the problem is i'm a newbie with c#.
the project we need to make is a survey. so the problem is my first question of the survey works fine when i push the go to next it enters the data nice into my sql db and my second question loads, then I enter the answer for my second question i press go to next question and i get this error: " The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Antwoorden_Gebruikers". The conflict occurred in database "GIP_enquete", table "dbo.Gebruikers", column 'Gebruiker_ID'. " I don't know how i can fix this cause im to newbie to understand whats wrong.

here is a view of my code:

Code Snippet

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using enqueteTableAdapters;

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
int Vraag_ID = Convert.ToInt16(Request.QueryString["vraagid"]);
VragenTableAdapter VraagAdapter = new VragenTableAdapter();
lblVraag.Text = Convert.ToString(VraagAdapter.GeefVraag(Vraag_ID));

if (Vraag_ID == 1)
{
pnlVraag1.Visible = true;
}

if (Vraag_ID == 2)
{
pnlVraag2.Visible = true;
}

}
protected void btnVraag1_Click(object sender, EventArgs e)
{
//make a new user
GebruikersTableAdapter GebruikerAdapter = new GebruikersTableAdapter();
DateTime Moment = System.DateTime.Now;
GebruikerAdapter.GebruikerToevoegen(Moment);
int GebruikerID = Convert.ToInt16(GebruikerAdapter.GeefGebruikerID(Moment));
this.ViewState.Add("gebruiker_id", GebruikerID);

//send answer
AntwoordenTableAdapter AntwoordAdapter = new AntwoordenTableAdapter();
AntwoordAdapter.AntwoordIngeven(1, GebruikerID, txtVraag1.Text, null);

//go to 2nd question
Response.Redirect("Default.aspx?vraagid=2", true);
}

protected void btnVraag2_Click(object sender, EventArgs e)
{
//get back the user from question 1
int GebruikerID = Convert.ToInt16(this.ViewState["gebruiker_id"]);

//send answer
AntwoordenTableAdapter AntwoordAdapter = new AntwoordenTableAdapter();
AntwoordAdapter.AntwoordIngeven(2, GebruikerID, txtVraag2.Text, null);

when i run debug it allways stops here

//go too 3th question
Response.Redirect("Default.aspx?vraagid=3", true);
}
}

can somebody plz help me ? =(

ps: sorry for my bad english

View 7 Replies View Related

ERROR:- An INSERT EXEC Statement Cannot Be Nested.

May 3, 2004

HI,

WELL WE HAVE BEEN TRYING TO AUTOMATE A PROCEDURE OUT HERE,AND WE ARE TRYING TO CONVERT MOST OF THE THINGS INTO PROCEDURES.

BUT WE ARE GETTING A FEW HICCUPS. PLS HELP

THIS IS HOW IT GOES :-

CREATE PROCEDURE MY_PROC1
AS
BEGIN
ST1 .........;
ST2..........;
END

CREATE PROCEDURE MY_PROC2
AS
BEGIN

CREATE TABLE #TMP2
(COL1 DATATYPE
COL2 DATATYPE)

INSERT INTO #TMP2
EXEC MY_PROC1

ST1 .........;
ST2..........;

END

THIS PROCEDURE TOO RUNS WELL ,AFTER TAKING THE DATA FROM THE FIRST PROC IT MANIPUATES THE DATA ACCORDING TO THE CRITERIA SPECIFIED

NO PROBLEM TILL NOW.......

BUT,

CREATE PROCEDURE MY_PROC3
AS
BEGIN

CREATE TABLE #TMP3
(COL1 DATATYPE
COL2 DATATYPE)

INSERT INTO #TMP3
EXEC MY_PROC2

ST1 .........;
ST2..........;

END

THEN IT GIVES AN ERROR AS :-

"An INSERT EXEC statement cannot be nested."

CAN'T WE , FROM A PROCEDURE CALL A PROCEDURE WHICH CALLS A PROCEDURE........

WHAT IS THE NESTING LEVEL OF A PROCEDURE ?

IS THERE ANY WAY AROUND IT OR CAN IT BE DONE BY CHANGING SOME SETTINGS ?

PLS HELP ME OUT IN THIS

THANKS

View 13 Replies View Related

An INSERT EXEC Statement Cannot Be Nested Error.

Nov 28, 2005

Hi,all,When I use following sql, an error occurs:insert into #tmprepEXECUTE proc_stat @start,@endThere is a "select * from #tmp " in stored procedure proc_stat, and theerror message is :Server: Msg 8164, Level 16, State 1, Procedure proc_stat, Line 42An INSERT EXEC statement cannot be nested.What's the metter? Any help is greatly appreciated. Thanks

View 2 Replies View Related

Error Running Insert Statement In A Database

Aug 15, 2007

When I run this query against PFGDSMRISSQLQ1 in Server manager 2005:use <database>INSERT INTO dbo.<table> (<column1>, <column2>, <column3>)VALUES (12598,2900,1.00)I get this error:Msg 8624, Level 16, State 1, Line 5Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.Can someone please help. Thanks!

View 1 Replies View Related

SELECT @@IDENTITY During INSERT STATEMENT Error

Mar 9, 2008

The following SQL statement fails on SQL CE 3.5 but works on SQL Express 2005:

"INSERT INTO BOOKINGS VALUES(@now,'"+note+"'," + p + "); SELECT @@IDENTITY;"

Compact 3.5 doesnt like the SELECT statement claiming that:

There was an error parsing the query. [ Token line number = 1,Token line offset = 72,Token in error = SELECT ]

Can anyone suggest the correct SQL to implement this via Compact? i.e. How do I retrieve the Identity value during and insert statement?

I have removed the SELECT @@IDENTITY; portion of the statement and it runs fine.

View 9 Replies View Related

SQL 2012 :: Select Permission Error On Insert Statement

Jul 28, 2015

Have run to a select permission error when attempting insert data to a table. received the following error

Msg 229, Level 14, State 5, Line 11
The SELECT permission was denied on the object 'tableName', database 'DBname', schema 'Schema'.

Few things to note
- There are no triggers depending on the table
- Permissions are granted at a roll level which is rolled down to the login
- The test environments have the same level of permission which works fine.

View 6 Replies View Related

Problem Using Result From CASE In Another CASE Statement

Nov 5, 2007

I have a view where I'm using a series of conditions within a CASE statement to determine a numeric shipment status for a given row. In addition, I need to bring back the corresponding status text for that shipment status code.

Previously, I had been duplicating the CASE logic for both columns, like so:

Code Block...beginning of SQL view...
shipment_status =
CASE
[logic for condition 1]
THEN 1
WHEN [logic for condition 2]
THEN 2
WHEN [logic for condition 3]
THEN 3
WHEN [logic for condition 4]
THEN 4
ELSE 0
END,
shipment_status_text =
CASE
[logic for condition 1]
THEN 'Condition 1 text'
WHEN [logic for condition 2]
THEN 'Condition 2 text'
WHEN [logic for condition 3]
THEN 'Condition 3 text'
WHEN [logic for condition 4]
THEN 'Condition 4 text'
ELSE 'Error'
END,
...remainder of SQL view...

This works, but the logic for each of the case conditions is rather long. I'd like to move away from this for easier code management, plus I imagine that this isn't the best performance-wise.

This is what I'd like to do:

Code Block
...beginning of SQL view...
shipment_status =
CASE
[logic for condition 1]
THEN 1
WHEN [logic for condition 2]
THEN 2
WHEN [logic for condition 3]
THEN 3
WHEN [logic for condition 4]
THEN 4
ELSE 0
END,

shipment_status_text =

CASE shipment_status

WHEN 1 THEN 'Condition 1 text'

WHEN 2 THEN 'Condition 2 text'

WHEN 3 THEN 'Condition 3 text'

WHEN 4 THEN 'Condition 4 text'

ELSE 'Error'

END,
...remainder of SQL view...

This runs as a query, however all of the rows now should "Error" as the value for shipment_status_text.

Is what I'm trying to do even currently possible in T-SQL? If not, do you have any other suggestions for how I can accomplish the same result?

Thanks,

Jason

View 1 Replies View Related

System.Data.OleDb.OleDbException: Syntax Error In INSERT INTO Statement.

May 3, 2007

Hi All I'm having a bit of trouble with an sql statement being inserted into a database - here is the statement: string sql1;
sql1 = "INSERT into Customer (Title, FirstName, FamilyName, Number, Road, Town,";
sql1 += " Postcode, Phone, DateOfBirth, email, PaymentAcctNo)";
sql1 += " VALUES (";
sql1 += "'" + TxtTitle.Text + "'," ;
sql1 += "'" + TxtForename.Text + "'," ;
sql1 += "'" + TxtSurname.Text + "'," ;
sql1 += "'" + TxtHouseNo.Text + "',";
sql1 += "'" + TxtRoad.Text + "',";
sql1 += "'" + TxtTown.Text + "',";
sql1 += "'" + TxtPostcode.Text + "',";
sql1 += "'" + TxtPhone.Text + "',";
sql1 += "'" + TxtDob.Text + "',";
sql1 += "'" + TxtEmail.Text + "',";
sql1 += "'" + TxtPayAcc.Text + "')"; Which generates a statement like:INSERT into Customer (Title, FirstName, FamilyName,
Number, Road, Town, Postcode, Phone, DateOfBirth, email, PaymentAcctNo) VALUES ('Mr','Test','Test','129','Test Road','Plymouth','PL5
1LL','07855786111','14/04/1930','mr@test.com','123456') I cannot for the life of me figure out what is wrong with this statement. I've ensured all the fields within the database have no validation (this is done within my ASP code) that would stop this statement being inserted. Line 158: dbCommand.Connection = conn;Line 159: conn.Open();Line 160: dbCommand.ExecuteNonQuery();Is the line that brings up the error - I presume this could be either an error in the statement or maybe some settings on the Database stopping the values being added. Any ideas which of this might be ? I'm not looking for someone to solve this for me, just a push in the right direction! Thanks!

View 2 Replies View Related