Syntax Error In UPDATE Statement.

Apr 13, 2004

I have place a lblmessage.text = ex.Message to trace what happen and it show me a syntax error in update happen. I don't know whats wrong with my syntax. Can someone help me on this?





<%@ Page Language = "vb" Debug="true" %>


<%@ import namespace= "system.data" %>


<%@ import namespace= "system.data.oledb" %>


<script runat="server">





'set up connection


dim conn as new oledbconnection _


("provider = microsoft.jet.oledb.4.0;" & _


"data source = c:aspnetdataanking.mdb")














sub page_load(sender as object, e as eventargs)


if not page.ispostback then


filldatagrid()


end if


end sub














sub submit (sender as object, e as eventargs)


'insert new data


dim i,j as integer


dim params(7) as string


dim strtext as string


dim blngo as boolean = true





j = 0





for i = 0 to addpanel.controls.count -1


if addpanel.controls(i).gettype is _


gettype (textbox) then


strtext = ctype(addpanel.controls(i), _


textbox).text


if strtext <> "" then


params(j) = strtext


else


blngo = false


lblmessage.text = lblmessage.text & _


"you forgot to enter a value for " & _


addpanel.controls (i).id & "<p>"


lblmessage.style ("forecolor")= "red"


end if


j=j+1


end if


next





if not blngo then


exit sub


end if





dim strsql as string = "INSERT INTO tblusers " & _


"(firstname, lastname, address, city, state, " & _


"zip, phone) values (" & _


"'" & params(0) & "'," & _


"'" & params(1) & "'," & _


"'" & params(2) & "'," & _


"'" & params(3) & "'," & _


"'" & params(4) & "'," & _


"'" & params(5) & "'," & _


"'" & params(6) & "')"





executestatement(strsql)


filldatagrid()


end sub














sub dgdata_edit (sender as object, e as datagridcommandeventargs)


filldatagrid(e.item.itemindex)


end sub














sub dgdata_delete (sender as object, e as datagridcommandeventargs)


dim strsql as string = "DELETE FROM tblusers " & _


"WHERE userid = " & e.item.itemindex + 1





executestatement(strsql)


filldatagrid()


end sub














sub dgdata_update (sender as object, e as datagridcommandeventargs)


if updatedatastore(e) then


filldatagrid(-1)


end if


end sub











sub dgdata_cancel (sender as object, e as datagridcommandeventargs)


filldatagrid(-1)


end sub











sub dgdata_pageindexchanged (sender as object, e as datagridpagechangedeventargs)


dgdata.databind()


end sub











function updatedatastore (e as datagridcommandeventargs) as boolean


dim i,j as integer


dim params(7) as string


dim strtext as string


dim blngo as boolean = true





j = 0





for i =1 to e.item.cells.count - 3


strtext = ctype(e.item.cells(i).controls(0), _


textbox).text


if strtext <> "" then


params(j) = strtext


blngo = true


j= j+1


else


blngo = false


lblmessage.text = lblmessage.text & _


"you forgot to enter a value<p>"


end if


next





if not blngo then


return false


exit function


end if





dim strsql as string = "update tblusers SET " & _


"Firstname.value = '" & params(0) & "'," & _


"lastname.value = '" & params(1) & "'," & _


"address.value = '" & params(2) & "'," & _


"city.value = '" & params(3) & "'," & _


"state.value = '" & params(4) & "'," & _


"zip.value = '" & params(5) & "'," & _


"phone.value = '" & params(6) & "'," & _


" WHERE Userid = " & ctype(e.item.cells(0). _


controls(1), label).text





executestatement (strsql)


return blngo


end function





sub filldatagrid (optional editindex as integer = -1)


' open connection


dim objcmd as new oledbcommand _


("select * from tblusers", conn)


dim objreader as oledbdatareader





try


objcmd.connection.open ()


objreader = objcmd.executereader()


catch ex as exception


lblmessage.text = "error retrieving from the " & _


"database."


end try





dgdata.datasource = objreader


if not editindex.equals(nothing) then


dgdata.edititemindex = editindex


end if





dgdata.databind()


objreader.close


objcmd.connection.close()





end sub





function executestatement (strsql)


dim objcmd as new oledbcommand(strsql, conn)





try


objcmd.connection.open()


objcmd.executenonquery()


catch ex as exception


lblmessage.text = ex.message


end try





objcmd.connection.close()


end function


</script>





<html><body>


<asp:label id= "lblmessage" runat ="server" />


<form runat= "server">


<asp:datagrid id = "dgdata" runat ="server"


bordercolor = "black"


gridlines="vertical"


cellpadding ="4"


cellspacing="0"


width = "100%"


autogeneratecolumns = "False"


ondeletecommand = "dgdata_delete"


oneditcommand = "dgdata_edit"


oncancelcommand = "dgdata_cancel"


onupdatecommand = "dgdata_update"


onpageindexchanged= "dgdata_pageindexchanged"


font-names = "arial"


font-size="8pt"


showfooter = "true"


headerstyle-backcolor= "#cccc99"


footerstyle-backcolor= "#cccc99"


itemstyle-backcolor= "#ffffff"


alternatingitemstyle-backcolor="#cccccc">





<columns>


<asp:templatecolumn headertext="id">


<itemtemplate>


<asp:label id = "name" runat = "server"


text = '<%# container.dataitem("userid")%>' />


</itemtemplate>


</asp:templatecolumn>





<asp:boundcolumn headertext = "firstname"


datafield = "firstname" />





<asp:boundcolumn headertext = "lastname"


datafield = "lastname" />





<asp:boundcolumn headertext = "address"


datafield = "address" />





<asp:boundcolumn headertext = "city"


datafield = "city" />





<asp:boundcolumn headertext = "state"


datafield = "state" />





<asp:boundcolumn headertext ="zip"


datafield = "zip" />





<asp:boundcolumn headertext ="phone"


datafield = "phone" />





<asp:editcommandcolumn


edittext="Edit"


canceltext="cancel"


updatetext="update"


headertext = "edit"/>





<asp:buttoncolumn headertext = "delete?" text = "X"


commandname = "delete"


buttontype = "pushbutton" />





</columns>


</asp:datagrid><p>





<asp:panel id= "addpanel" runat = "server">


<table>


<tr>


<td width ="100" valign = "top">


first and last name:


</td>


<td width ="300" valign = "top">


<asp:textbox id= "tbfname" runat = "server"/>


<asp:textbox id= "tblname" runat = "server"/>


</td>


</tr>


<tr>


<td valign = "top">address:</td>


<td valign= "top">


<asp:textbox id= "tbaddress" runat = "server"/>


</td>


</tr>


<tr>


<td valign = "top">city, state, zip:</td>


<td valign= "top">


<asp:textbox id= "tbcity" runat = "server"/>


<asp:textbox id= "tbstate" runat = "server"/>


<asp:textbox id= "tbzip" runat = "server"


size=5 />


</td>


</tr>


<tr>


<td valign= "top">phone:</td>


<td valign= "top">


<asp:textbox id= "tbphone" runat = "server"


size = 11/><p>


</td>


</tr>


<tr>


<td colspan = "2" valign = "top" allign = "right">


<asp:button id = "btsubmit" runat ="server" text ="add"


onclick = "submit" />


</td>


</tr>


</table>


</asp:panel>


</form>


</body></html>

View 14 Replies


ADVERTISEMENT

Syntax Error In UPDATE Statement.

May 2, 2008

Hi forum, im converting some code and have an issue with th following code!!! many thanks for good advice, Paul
Line 55:     SQLa = "UPDATE counted SET " & y & " = " & intClick & ", total = " & intTotal & " WHERE ID = " & RSpc.Fields("pc").ValueLine 56:     Conn.Execute(SQLa)
System.Runtime.InteropServices.COMException: Syntax error in UPDATE statement.
Dim SQLa As String 

View 8 Replies View Related

UPDATE Statement Syntax Help Required C# && SqlDataSource Control

May 3, 2007

Hi,
I need to UPDATE the IP Address of a newly created user into a table (the value is currently set to default - " Not Available"), and I really dont know the syntax required to do this. So far I've derived all the variables needed using the following code:
protected void ContinueButton_Click(object sender, EventArgs e)
{
//Get the ip address and put it into the customer table - (the instance of this user now exists)
MembershipUser _membershipUser = Membership.GetUser();
Guid UserId = (Guid)_membershipUser.ProviderUserKey;<--------------------------------------------------------------I can see the UserId here if I pause the prog
SqlDataSource customerDataSource = new SqlDataSource();
customerDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
customerDataSource.UpdateParameters.Add("IPAddress", Request.UserHostAddress.ToString());<---------------------------------------I can see the IPAddress here
customerDataSource.UpdateCommandType = SqlDataSourceCommandType.Text;
 what next
I've not got a clue as to what to write next. There is a try / catch statement after this using :
rowsAffected = customerDataSource.Update();
which remains at 0 no matter what I try.
Any help greatly appreciated.

View 15 Replies View Related

SQL Statement Syntax Error?

Apr 22, 2008

SELECT DISTINCT CONVERT (nvarchar , tblSubject.Subject, 108) AS SubjectTime, CONVERT (nvarchar(11), tblSubject.Subject, 100) AS Date FROM tblLooker,tblSubject,tblStop WHERE NOT (SELECT DISTINCT CONVERT (nvarchar , tblSubject.Subject, 108) AS SubjectTime, CONVERT (nvarchar(11), tblSubject.Subject, 100) AS Date FROM tblSubject, tblLooker, tblStop WHERE tblSubject.Subject > tblLooker.Looker AND tblSubject.Subject < tblStop.Stop AND tblLooker.id = tblStop.id) Msg 170, Level 15, State 1, Line 5Line 5: Incorrect syntax near ')'. Can anyone tell me why this is not working? Thanks   

View 1 Replies View Related

If Statement ... Syntax Error

Apr 9, 2008

hi, i've got this code





Code Snippet

go

create proc dbo.sp_GetFilterOrgIDs(@ID int)

as

select distinct


O.ID

from


Organisations O

if (@ID != 0)

begin


where O.ID = @ID
end






and am getting


Incorrect syntax near the keyword 'where'.

any idea why??

View 6 Replies View Related

IIF Statement Has Syntax Error...please Help...

May 20, 2008

I am trying to figure out the syntax for the following:

=iif(fields!ExtTripCount.value = 1,sum(Fields!ExtTripCount.value),0) / (count(fields!SvcCallNumber.value))* 100

I am trying to take all the trips that equal a one and divide them by the total number of service calls and then multiple them by 100 to get the percentage.

I am receiving the following errror: "The value expression refers to the field 'SvcCallNumber'. Report item expressions can only refer to fields within the current dta set scope or, if inside an aggregate, the specified data set scope...

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:
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; sqlCommand = New SQLCommand("INSERT INTO Bulk (Bulk_Run, Bulk_Totes, Bulk_Drums, Bulk_Boxes, Bulk_Bags, Bulk_Bins, Bulk_Crates) VALUES (" &amp; RunList(x,0) &amp; ", " &amp; Totes &amp; ", " &amp; Drums &amp; ", " &amp; Boxes &amp; ", " &amp; Bags &amp; ", " &amp; Bins &amp; ", " &amp; Crates &amp; ")", Connection)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; sqlCommand.ExecuteNonQuery()&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; sqlCommand = New SQLCommand("INSERT INTO Presort (Presort_Run, Presort_Totes, Presort_Drums, Presort_Boxes, Presort_Bags, Presort_Bins, Presort_Crates) VALUES (" &amp; RunList(x,0) &amp; ", " &amp; Totes &amp; ", " &amp; Drums &amp; ", " &amp; Boxes &amp; ", " &amp; Bags &amp; ", " &amp; Bins &amp; ", " &amp; Crates &amp; ")", Connection)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 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

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

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

UPDATE Syntax Error

Apr 10, 2007

Hello
I am trying to update a column containing URL's and include the "www." which had previously been omitted on many URL's in the column. But I get an error when trying to UPDATE
I have tried:
UPDATE table_nameSET URL = http://www.a2zdom.com/*WHERE URL = http://a2zdom.com/*
I have tried and left out the http: and also the /* but nothing works.  Is this type of update not possible?
Thanks

View 7 Replies View Related

Update Syntax Error

Jun 12, 2007

           
string cmdTxt = "Update penberry_SubjectName set SubjectLeaderId IN (
SELECT userid FROM aspnet_users WHERE username = @subjectLeaderName)
where SubjectCode = @subjectCode";

            SqlConnection sqlconn = new SqlConnection(sqlConnStr);
            SqlCommand sqlCmd = new SqlCommand(cmdTxt, sqlconn);

           
sqlCmd.Parameters.AddWithValue("@subjectLeaderName", subjectLeaderName);
            sqlCmd.Parameters.AddWithValue("@subjectCode", subjectCode);

            sqlconn.Open();
            sqlCmd.ExecuteNonQuery(); hi guys i got this error from my program.Can anyone help me out?

View 9 Replies View Related

Update Syntax Error

Dec 5, 2000

hi, someone please tell me what's wrong with this update statement
It's supposed to get values for employee location from a test table, based on the first name and last name match, and update the outer table. I get this error...

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'a'.



update abs_employee a
set a.office_location =
(select b.Location
from abs_emptest b
where b.lastname = a.last_name and
b.firstname = a.first_name)

View 2 Replies View Related

Syntax Error In Update?

Jul 23, 2005

Hi all,I have just written a sql statement where I want two fields updatedfrom another table. The statement like so:update kpidmatterset clientcode,clientname = (select clientcode, clientnamefrom clientconversionwhere (clientcode = kpidmatter.clintcode))where exists(select clientcode, clientnamefrom clientconversionwhere (clientcode = kpidmatter.clintcode))This refuses to work.The statement I based this on worked (below) but only updated onefield (teach me for being too ambitions!). Can anyone see anythingobvious? I know that a DDL and sample data is far more useful but inthis case I think there's a simple syntax problem.I'd be most grateful if someone could spot my error.SamWorking sample:UPDATE kpidmatterSET clientpartner = (SELECT ContactNameFROM newnamesWHERE (feeearnercode = kpidmatter.clientpartnercode))where exists(SELECT ContactNameFROM newnamesWHERE (feeearnercode = kpidmatter.clientpartnercode))

View 6 Replies View Related

Syntax Error With &#34;alter Table&#34; Statement

Mar 27, 2001

Folks!

What is wrong with my syntax with the following command?:

alter table EmployeeInfo alter column OriginDate smalldatetime not null default getdate()

I'm getting:

Incorrect syntax near the keyword 'default'

Currently, in my table OriginDate is nullable with no default.
Thanks in advance for your help!

APF

View 2 Replies View Related

Syntax Error With Joined Update

Mar 24, 2008

Hello all,
 Iv been making a lot of progress on my first functional webapp, but I cannot get this bit of code to work correctly.  I think my UPDATE SQL statement is where the problem is.  It works fine the first time through when there is no Session("estimateid") set, but after that is set it gives me error this on line 40:
Incorrect syntax near '('.
 1 Dim CustID As Integer
2
3 Dim DbConnection As SqlConnection
4 DbConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("harringtonairdb").ConnectionString)
5 DbConnection.Open()
6 Dim DbCommand As SqlCommand
7
8 If Session("estimateid") = 0 Then
9 Dim DbSqlInsert As String
10 DbSqlInsert = "INSERT INTO tblcustomers (strname, strAddress1, strAddress2, strCity, strState, strZip, strPhone, strEmail, strContact) VALUES (@name, @address1, @address2, @city, @state, @zip, @phone, @email, @contact)" & "SELECT @@IDENTITY AS CustID"
11 DbCommand = New SqlCommand(DbSqlInsert, DbConnection)
12 Else
13 Dim DbSqlUpdate As String
14 DbSqlUpdate = "UPDATE tblcustomers SET (strcustname=@name, straddress1=@address1, straddress2=@address2, strcity=@city, strstate=@state, strzip=@zip, strphone=@phone, stremail=@email, strcontact=@contact) JOIN tblestimates ON pkcustomerid WHERE pkestimateid=@estimateid"
15 DbCommand = New SqlCommand(DbSqlUpdate, DbConnection)
16 DbCommand.Parameters.AddWithValue("@estimateid", Session("estimateid"))
17
18 End If
19
20 DbCommand.Parameters.AddWithValue("@name", txtCustName.Text)
21 DbCommand.Parameters.AddWithValue("@address1", txtCustAddress1.Text)
22 DbCommand.Parameters.AddWithValue("@address2", txtCustAddress2.Text)
23 DbCommand.Parameters.AddWithValue("@city", txtCustCity.Text)
24 DbCommand.Parameters.AddWithValue("@state", txtCustState.Text)
25 DbCommand.Parameters.AddWithValue("@zip", txtCustZip.Text)
26 DbCommand.Parameters.AddWithValue("@phone", txtCustPhone.Text)
27 DbCommand.Parameters.AddWithValue("@email", txtCustEmail.Text)
28 DbCommand.Parameters.AddWithValue("@contact", txtCustTimes.Text)
29
30
31 If Session("estimateid") = 0 Then
32 CustID = Convert.ToInt32(DbCommand.ExecuteScalar())
33 DbCommand.Dispose()
34 Dim DbSqlInsert As String
35 DbSqlInsert = "INSERT INTO tblestimates (fkcustomerid) VALUES (@customerid)" & "SELECT @@IDENTITY AS EstimateID"
36 DbCommand = New SqlCommand(DbSqlInsert, DbConnection)
37 DbCommand.Parameters.AddWithValue("@customerid", CustID)
38 Session.Add("estimateid", Convert.ToInt32(DbCommand.ExecuteScalar()))
39 Else
40 DbCommand.ExecuteNonQuery()
41 End If
42
43 DbConnection.Close()
44 DbCommand.Dispose()
 

View 3 Replies View Related

On DELETE On UPDATE Cascade Syntax Error

Dec 13, 2006

Hello
I need to be able to regularly, update or delete data from my parent table and subsequent child tables from A to Z, each table contains data.  However, I have having problems.
I have already created  the tables with primary keys on each table and foreign keys linking each table to the next.
I tried to delete a row from the parent table and was given this error:
DELETE FROM [dbo].[DomNam]WHERE [DomNam]=N' football '
Error: Query(1/1) DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_DomNam'. The conflict occurred in database 'DomDB', table 'Dom_CatA', column 'DomNam'.
 
I tried to insert an alter table query:
ALTER TABLE dbo.DomNamADD CONSTRAINT FK_Dom_ID
REFERENCES dbo.Dom_CatA (Dom_ID)
ON DELETE CASCADE ON UPDATE CASCADE
But on Execute I saw this error:
Error]  Incorrect syntax for definition of the 'TABLE' constraint
What is wrong with the above syntax?
Or would it be better if I used a trigger instead because I already have foreign keys set within the tables?If so please give an example of the syntax for the trigger I would need to update and cascade data from all tables. 
I would be grateful for any advice.  Thanks.
 
 
 

View 8 Replies View Related

Error Incorrect Syntax Near '('. When Doing Update() From Code, VB

Apr 22, 2007

Hi all
My error is as follows: Incorrect syntax near '('.Line 27:         acceptOrDeclineFriendship.UpdateParameters.Add("Response", answer)Line 28:         acceptOrDeclineFriendship.UpdateParameters.Add("FriendID", friend_id)Line 29:         acceptOrDeclineFriendship.Update()Line 30: Line 31:     End Sub
Bear with me... I have a page where i use a repeater control to list users who have requested to be friends with the currently online user. The 'getFriendRequests' query looks like this:
SelectCommand="SELECT * FROM Friends, UserDetails WHERE (Friends.UserID = UserDetails.UserID) AND (FriendID = @UserID) AND (ApprovedByFriend = 'False') ORDER BY Friends.Requested DESC">This works. 
Within each repeater template, there are 2 buttons, 'Accept' or 'Decline', like this: <asp:Repeater ID="Repeater1" runat="server" DataSourceID="getFriendRequests">
<ItemTemplate>
(other stuff like avatar and username etc)
<asp:Button ID="accept" runat="server" Text="Accept" commandName="Accept" commandArgument='<%#Eval("UserID")%>' onCommand="Accept_Decline_Friends"/>
<asp:Button ID="decline" runat="server" Text="Decline" commandName="Decline" commandArgument='<%#Eval("UserID")%>' onCommand="Accept_Decline_Friends"/>
</ItemTemplate>
</asp:Repeater>
The code-behind (VB) which deals with this is as follows: Protected Sub Accept_Decline_Friends(ByVal sender As Object, ByVal e As CommandEventArgs)

'retrieve id of requestee and the answer accept/decline
Dim friend_id As String = e.CommandArgument.ToString
Dim answer As String = e.CommandName.ToString

'add the parameters
acceptOrDeclineFriendship.UpdateParameters.Add("Response", answer)
acceptOrDeclineFriendship.UpdateParameters.Add("FriendID", friend_id)
acceptOrDeclineFriendship.Update()

End Sub
Since the buttons are being created dynamically, this is how i track 1. the response from the currently logged in user 'Accept/Decline' and 2. who they are responding to (by their uniqueid)
This relates to a sqlDataSource on my .aspx page like this: <!---- update query when user has accepted the friendship ---->
<asp:SqlDataSource ID="acceptOrDeclineFriendship" runat="server" ConnectionString="<%$ xxx %>"
UpdateCommand="UPDATE Friends SET (ApprovedByFriend = @Response) WHERE (FriendID = @UserID) AND (UserID = @FriendID)">
<UpdateParameters>
<asp:ControlParameter Name="UserID" ControlID="userIdValue" />
</UpdateParameters>
</asp:SqlDataSource>
 Which is meant to update my 'Friends' table to show that 'ApprovedByFriend' (the logged in user) is either 'Accept' or 'Decline', and record who's request was responded to.
I hope this is clear, just trying to suppy all of the information! The error appears to be saying that I have an issue with my code-behind, where i am telling the sqlDataSource above to UPDATE. What I can say is that for each button in the repeater, the 2 variables 'friend_id' and 'answer' are picking up the correct values.
Can anyone see any obvious problems here? Any help is very much appreciated as i am well and truley stuck!

View 1 Replies View Related

Incorrect Syntax Near The Keyword 'EXEC' (Dynamic Sql Statement Error)

Nov 1, 2007

Following is the stored procedure iam trying to create.Here i am trying to

First create a table with the table name passed as parameter
Second I am executing a dynamic sql statement ("SELECT @sql= 'Select * from table") that returns some rows.
Third I want to save the rows returned by the dynamic sql statement ("SELECT @sql= 'Select * from table") in the tablei created above.All the columns and datatypes are matching.

This table would be further used with cursor.
Now i am getting a syntax error on the last line.Though i doubt whether the last 3 lines will execute properly.Infact how to execute a sp_executesql procedure in another dynamic sql statement.ANy suggestions will be appreciated.


CREATE PROCEDURE [dbo].[sp_try]

@TempTable varchar(25)


AS

DECLARE @SQL nvarchar(MAX)

DECLARE @SQLINSERT nvarchar(MAX)



BEGIN


--create temp table

SELECT @Sql= N'CREATE TABLE ' + QUOTENAME(@TempTable) +

'(

ContactName varchar (40) NOT NULL ,

ContactId varchar (30) NOT NULL ,

ContactrMessage varchar (100) NOT NULL,



)'

EXEC sp_executesql @Sql, N'@TempTable varchar(25)', @TempTable = @TempTable


SELECT @sql= 'Select * from table'




SELECT @sqlinsert = 'INSERT INTO ' + quotename( @TempTable )

SELECT @sqlinsert = @sqlinsert + EXEC sp_executesql @sql, N'@Condition varchar(max)', @Condition=@Condition

EXEC sp_executesql @SQLINSERT, N'@TempTable varchar(25)', @TempTable = @TempTable

View 8 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

Exception Error - Incorrect Syntax Near '('. Inline UPDATE Command

May 4, 2007

Hi,
Here's the code I've used to try and update a new user's IP Address to a Table called Customer who's key field in the UserId:
Getting the Exception Error   "Incorrect Syntax near'('. "                 Any ideas?
protected void ContinueButton_Click(object sender, EventArgs e)
{
//Get the ip address and put it into the customer table - (the instance of this user now exists)
 
MembershipUser _membershipUser = Membership.GetUser(); //This gets the active user if there is someone logged in...
Guid UserId = (Guid)_membershipUser.ProviderUserKey; //This gets the userId for the currently logged in user
string IPAddress = Request.UserHostAddress.ToString();//This gets the IPAddress of the currently logged in user
string cs = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
using (System.Data.SqlClient.SqlConnection con =new System.Data.SqlClient.SqlConnection(cs))
{
con.Open();
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.Text;
 
cmd.CommandText = "UPDATE Customer SET(IP_Address = @IP_Address) WHERE (UserId = @UserId)";
cmd.Parameters.Add("@UserId", System.Data.SqlDbType.UniqueIdentifier).Value = UserId;
cmd.Parameters.Add("@IP_Address", System.Data.SqlDbType.Char, 15).Value = IPAddress;
cmd.ExecuteNonQuery();
 
con.Close();
}
 Thanks.

View 5 Replies View Related

CTE Error: Incorrect Syntax Near The Keyword 'with'. If This Statement Is A Common Table Expression Or An Xmlnamespaces Clause,

Aug 3, 2006

I am having this error when using execute query for CTE

Help will be appriciated

View 9 Replies View Related

UPDATE Statement Error

Mar 30, 2006

Im getting this error when i try to change my value of my int RoleID: UPDATE statement conflicted with COLUMN FOREIGN KEY
constraint 'Roles_Users_FK1'. The conflict occurred in database
'lyngso', table 'Roles', column 'RoleID'.The statement has been terminated.



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.SqlClient.SqlException:
UPDATE statement conflicted with COLUMN FOREIGN KEY constraint
'Roles_Users_FK1'. The conflict occurred in database 'lyngso', table
'Roles', column 'RoleID'.The statement has been terminated.

Source Error:




Line 373: tryLine 374: {Line 375: rowsAffected = dbCommand.ExecuteNonQuery();Line 376: }Line 377: finallyThe code that courses this error should be this code from my DatabaseHandler class:... queryString = "SELECT unitID FROM Units WHERE containerID = @containerID AND logDateTime = @logDateTime";        dbCommand = new SqlCommand(queryString, dbConnection);        //ContainerID Param        IDataParameter dbParam_containerID = new SqlParameter();        dbParam_containerID.ParameterName = "@containerID";        dbParam_containerID.Value = containerID;        dbParam_containerID.DbType = System.Data.DbType.String;        dbCommand.Parameters.Add(dbParam_containerID);        //LogDateTime Param        IDataParameter dbParam_logDateTime = new SqlParameter();                dbParam_logDateTime.ParameterName = "@logDateTime";        dbParam_logDateTime.Value = logDateTime;        dbParam_logDateTime.DbType = System.Data.DbType.DateTime;        dbCommand.Parameters.Add(dbParam_logDateTime);...The logDateTime param comes as a param to the method this code i located in. In my database the two tables are created as the following:CREATE TABLE [dbo].[Roles] (    [RoleID] [int] IDENTITY (1, 1) NOT NULL ,    [Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,    [RoleLevel] [int] NOT NULL ) CREATE TABLE [dbo].[Users] (    [UserID] [int] IDENTITY (1, 1) NOT NULL ,    [Firstname] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,    [Lastname] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,    [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,    [Phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,    [MobilPhone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,    [IsActive] [bit] NOT NULL ,    [RoleID] [int] NOT NULL ) To me it all looks fine, but my application doesn't agree with me....can anybody help here?

View 3 Replies View Related

Update Statement Error

May 26, 2004

I am getting an error message when trying to run this update statement:

update table1
set col1 = (select substring(col2,1,2) + '-' + substring(col2,3,7) from table1)

Error message:

Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

What am I doing wrong? Please help. Thanks.

View 5 Replies View Related

Update Statement Error

May 10, 2007

Hi All,

I have a following update statement that returns an error:

update job_schedule_rep
set tlbkup = (select Right(Convert(VarChar(30), Convert(DateTime, Stuff(Stuf
f(Right(Replicate('0', 6) + Convert(VarChar(8), tl.active_start_time), 6), 3
,0, ':'), 6, 0, ':')), 100),7) as 'Transaction Log Backup'
from
(select name, job_id, active_start_time
from sysjobschedules
where name like 'TLBkup%') as tl
join
(select name, job_id
from sysjobs
where name like 'TL Backup%') as sjt
on sjt.job_id = tl.job_id
where sjt.name not like '%Maintenance%'
order by sjt.name)

Here is an error:

Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.


I have tried replacing '=' with in and exists but it didn't make any differe
nce.

How can I fix it?

Thanks.

View 5 Replies View Related

Error Using UPDATE Statement

Feb 27, 2006

Hi,I am relatively new to SQL. I am using SQL 2000. I am trying toUpdate a field base in a criteria in a scond table.UPDATE Tbl1SET Tbl1.Row2 = '1'WHERE Tbl1.Row1 =(SELECT Tbl1.Row1FROM Tbl2, Tbl1WHERE Tbl2.Row1 = Tbl1.Row1 AND ({ fnCURRENT_TIMESTAMP () } >= Tbl2.Row3))Row 1 is the key between the two table. If I am doing only the selectbelow, I am getting the right value.SELECT Tbl1.Row1FROM Tbl2, Tbl1WHERE Tbl2.Row1 = Tbl1.Row1 AND ({ fnCURRENT_TIMESTAMP () } >= Tbl2.Row3)When I am running the entire querry, I am getting this error:Subquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery is used asan expression.The statement has been terminated.What I am trying to do is to update a field in Tbl1 base on a date inTbl2. If the date is expire, I want to raise a flag, in Tbl1.Thank youPhilippe

View 4 Replies View Related

Transact SQL :: Update Statement Run Error

Oct 1, 2015

I keep getting an error of error in statement near = on this update statement.  What is incorrect?

Update abcd
Set CAST(field1 As varchar(4000)) = 'Computers and 2-in-1s'
where CAST(field1 As varchar(4000)) = 'Computeres and and 2-in-1s'

View 11 Replies View Related

Update Statement Returns Error Due To Duplicates

Feb 26, 2008

Hi All,
When I use the following I get an error because of duplicate records in my table.

Update person
Set username = (Select update_person

View 2 Replies View Related

Update Statement Returns Error Due To Duplicates

Feb 26, 2008

Hi All,
When I use the following I get an error. I think it is because of duplicate records in my table.

Update person
Set username = (Select username
From update_person
Where person.firsname = update_person.firstname
and person.lastname = update_person.lastname)

There are a few users that have the same first and last name. How can I ignore the duplicate records and continue to update the table?

Thanks in advance.

View 13 Replies View Related

SQLS7&&VB6 Date Update Gives Syntax (Not Date Format) Error In Stored Procedure

Jul 20, 2005

Hi,I have a problem with updating a datetime column,When I try to change the Column from VB I get "Incorrect syntax near'942'" returned from [Microsoft][ODBC SQL Server Driver][SQL Server]'942' is the unique key column valueHowever if I update any other column the syntax is fineThe same blanket update query makes the changes no matter what isupdatedThe problem only happens when I set a unique key on the date field inquestionKey is a composite of an ID, and 2 date fieldsIf I allow duplicates in the index it all works perfectlyI am trying to trap 'Duplicate value in index' (which is working onother non-date columns in other tables)This is driving me nutsAny help would be appreciated

View 5 Replies View Related

If STATEMENT Within Select Statement Syntax

May 15, 2008

Hi,

I am a newbie to this site and hope someone can help....

I have a select statement which I would like to create an extra column and put an if statement in it.... Current syntax is:

if(TL_flag= '1', "yes") as [Trial Leave]

it is coming up with an error.... I can use Select case but I should not need to as this should work?

Any ideas?

View 2 Replies View Related

Update Syntax For Same Table Update

Aug 7, 2003

How do I write an update query to update a column in TabA with the information from other records in TabA?

View 2 Replies View Related

Multiple Tables Used In Select Statement Makes My Update Statement Not Work?

Aug 29, 2006

I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly.  My problem is that the table I am pulling data from is mainly foreign keys.  So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys.  I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit.  I run the "test query" and everything I need shows up as I want it.  I then go back to the gridview and change the fields which are foreign keys to templates.  When I edit the templates I bind the field that contains the string value of the given foreign key to the template.  This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value.  So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors.  I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode.  I make my changes and then select "update."  When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing.  The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work.  When I remove all of my JOIN's and go back to foreign keys and one table the update works again.  Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People].  My WHERE is based on a control that I use to select a person from a drop down list.  If I run the test query for the update while setting up my data source the query will update the record in the database.  It is when I try to make the update from the gridview that the data is not changed.  If anything is not clear please let me know and I will clarify as much as I can.  This is my first project using ASP and working with databases so I am completely learning as I go.  I took some database courses in college but I have never interacted with them with a web based front end.  Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian 

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved