SQL INSERT Syntax Madness
May 14, 2008
Hello. I have the below INSERT statement in an application, which works fine. However, I can't figure out how to insert data into a new column. The new column is text3, and the text would be TextBox4.Text + / + TextBox5.Text + / + TextBox6.Text + / TextBox7.Text. Where the "/" is text and not an operator. Every syntax I try doesn't work. Any ideas would be appreciated!
INSERT INTO table (updateTime, text4, text5, text6, text7, updateTime1) VALUES (getdate(), '" + this.TextBox4.Text + "', '" + this.TextBox5.Text + "', '" + this.TextBox6.Text + "', '" + this.TextBox7.Text + "', CONVERT(VARCHAR(19), GETDATE(), 120) + Space(2) )
View 5 Replies
ADVERTISEMENT
Jun 15, 2004
Hi all...I have a friend who has a problem I'm trying to help with (no...REALLY...it's a FRIEND, not ME!!! *LOL*)
I haven't run across the need...but in a nutshell...we have a table with many rows of data, one column in each row needs to be multiplied by all other same-columns in the table's other rows.
For example...
MyKey int, MyFloat float(53)
I want to multiply Myfloat by all other Myfloat columns in the table.
Similar to SUM(MyFloat) but something like PRODUCT(MyFloat).
Is there a aggregate kept in a basement closet somewhere, or a way to perform this operation rather than using a cursor to do it:
An example of my table:
1 3.2
2 4.1
3 7.1
if I could do a PRODUCT(MyFloat) I would want the result to be (3.2 X 4.1 X 7.1) or 93.152
Do I have to do this with a cursor?
Thanks!
Paul
View 9 Replies
View Related
Aug 31, 2007
Hello, I'll try to make the description of this multi-problem scenario as short and sweet as possible.
First Problem
The default named instance of "SQLEXPRESS" was somehow installed on my machine and nothing can get rid of it. However, it is somehow invisible to everything else on my system, so I can't use it. How do I get rid of the instance or repair it?
Second problem
I installed another instance of the SQL Server 2005 Express and named it, "SQLEXPRESS02". I attached a database to it and successfully retrieved data from it using an application that used the following connection string:
Data Source=(local)sqlexpress02;AttachDbFilename=|DataDirectory|MyDatabase.mdf;Initial Catalog=MyDatabase;Integrated Security=True;User Instance=True
Next, I tried to use a different application to retrieve data from a copy of the same database located in a different directory and used the same connection string as that shown above. When I do this, I receive an error message similar to the following:
Database 'C:PathToMyOriginalProjectinDebugMyDatabase.mdf' already exists. Choose a different database name.
Cannot attach the file 'C:PathToMyNewProjectinDebugMyDatabase.mdf' as database 'MyDatabase'.
I then tried, out of curiosity, to change the Initial Catalog portion of the connection string to "MyDatabase2" and attempt the data retrieval again. The connection string looked like this:
Data Source=(local)sqlexpress02;AttachDbFilename=|DataDirectory|MyDatabase.mdf;Initial Catalog=MyDatabase2;Integrated Security=True;User Instance=True
Interestingly enough, it worked. However, when I switched back to the original connection string, I got the same error message as before.
What's going on here? I thought that one of the virtues of user instances was that they would attach at run-time, then disconnect when the application was through with them. If that's the case, then why am I getting errors stating that a database of the same name is already attached? Is there a way to detach these databases from a user instance? Can it be done by some value in the connection string?
Thank you in advance for your help.
View 4 Replies
View Related
Oct 21, 2007
Hi all
Execute insert normal insert script Insert Into () select * from...
I need to monitor how many record inserted in target table, anyone can help
View 6 Replies
View Related
Mar 1, 2004
In SQL 7.0 SP3 , I am receiving this message....
Server: Msg 170, Level 15, State 1, Line 17
Line 17: Incorrect syntax near ')'.
When I try to execute this code from SQL Query Analyzer...
DECLARE @DPPNumberCursor INT
DECLARE DPPNumberCursor Cursor for Select PPAP_ID
from ppap
where ppap_cancel <> "1" and
ppap_close <> "1" and
projectonhold <> "1"
OPEN DPPNumberCursor
Fetch Next From DPPNumberCursor
INTO @dppnumbercursor
While @@Fetch_Status = 0
Begin
INSERT INTO APQPSubformTable (apqpsub_id)
Values (@dppnumbercursor)
Bsically, I want to insert the number held in @dppnumbercursor in the APQPSub_id field.
Any help would be appreciated.
View 10 Replies
View Related
Mar 20, 2007
INSERT INTO Payment_Breakdown (Order_ID,Denomination,Card_Payment,Comms_Request,Comms_Approved) VALUES (99109760,FAILED_CARD,0,-cc826 -cu826 -mc540436503249096 -tr09 -an1900 -cd4444333322221111 -dat070319172240 -ed0801 -is5 -rfIVR991097600 -sc456 -sd0501 -x,-rc30 -tr09 -ms"BAD AMOUNT" -rfIVR991097600 -cd4444333322221111 -ed0801 -sd0501 -cnVISA -td03132329 -mc540436503249096 -st0007 -x)
The element in italics is the problem I suspect. It is a text string to be entered into the table. It is actually a variable, I have taken this from a log as it shows the value instead.
I get this error...
quote:(STMT SQL Info:SQLPrepare [ S1000] Code:-201, [DataDirect][ODBC SequeLink driver][ODBC Socket][DataDirect][ODBC FileMaker driver][FileMaker]Parse Error in SQL q:F) (0xffffffff)
I am assuming the problem lies in the inverted commas (") in the italic string, is this right? How can I get around this or will I have to code them out before they become a string value in the variable?
View 2 Replies
View Related
Aug 21, 2005
HelloCan anyone help me translate this from access so that it can work in mssql(i need to get next value, but cannot use identity as if row is deleted,another must get new next column number which would be same as deleted one)Access;INSERT INTO tableSELECT(IIF(code<>Null,MAX(code)+1,1) AS code,0 AS usercodeFROM tableI tried this in mssql but will not work:INSERT INTO tableSELECTCASEWHEN code IS NULL THEN 1ELSE MAX(code)+1ENDAS code,0 AS usercodeFROM table
View 14 Replies
View Related
Dec 26, 2006
I am setting up a new database using a shopping cart SW, when I create the DB
using there script, I get the following error
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '('.
The line syntax is as follows.
BULK INSERT testDB.dbo.[Look-Weight] FROM 'C:Inetpubwwwroot estSQL-AdminLook-Weight.csv';
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ','
)
GO
What is wrong???
View 1 Replies
View Related
Mar 5, 2008
I have run into a problem with my instert satement that gives me a syntax error whenever i try and add an extra value to it.
This one works but
Code Snippet
string sqlString1 = "INSERT INTO QuoteHeader (QuoteHeaderID, CustomerID, EmployeeID) VALUES("
+ newOrderHeader
+ ",'" + custID
+ "','" + empID
+ "')";
add another value and I get a synax error And i'm not sure why.
Code Snippet
string sqlString1 = "INSERT INTO QuoteHeader (QuoteHeaderID, CustomerID, EmployeeID, Month) VALUES("
+ newOrderHeader
+ ",'" + custID
+ "','" + empID
+ "','" + textBox6.Text
+ "')";
Code Snippet
try
{
cmd = new OleDbCommand(sqlString1, connectCmd);
int rowsReturned = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
string s = ex.Message;
DisposeResources(ref oleDbDataAdapter, ref ds, ref connectFill, ref connectCmd, ref cmd);
return;
}
View 7 Replies
View Related
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
Apr 12, 2007
I am so close to haveing the web page i want, but i hve a syntax error with the Public Function Insert() as Integer.
I see no reason for this
i will post the code below, any help will be appreciated
<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" %>
<script runat="server">
Private Sub Submitdata(ByVal Source As Object, ByVal e As EventArgs)
SqlDataSource1.Insert()
End Sub ' Submitdata
</script>
<asp:Content ID="Content1" runat="server" ContentPlaceHolderID="ContentPlaceHolder1">
<asp:sqldatasource
id="SqlDataSource1"
runat="server"
connectionstring="<%$ ConnectionStrings:clientInfoConnectionString %>"
selectcommand="SELECT Client Name,Client Address FROM Clients"
insertcommand="INSERT INTO Clients (Client Name,Client Address) VALUES (@CName,@CAddress)">
<insertparameters>
<asp:formparameter name="CName" formfield="Namebox" />
<asp:formparameter name="CAddress" formfield="Addressbox" />
</insertparameters>
</asp:sqldatasource>
Name:<br />
<asp:TextBox ID="Namebox" runat="server" /><br />
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="Namebox"
ErrorMessage="Please Enter A Name"></asp:RequiredFieldValidator>
<br />Address:<br />
<asp:TextBox ID="Addressbox" runat="server" /><br />
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="Addressbox"
ErrorMessage="Please Enter An Address"></asp:RequiredFieldValidator>
<br /><asp:Button ID="Submitbtn" runat="server" Text="Submit" OnClick="InsertShipper" />
<br />
</asp:Content>
Thanks
The King
View 10 Replies
View Related
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
Sep 29, 1999
I am writing a stored procedure:
CREATE PROCEDURE rasp_FillDescrColumnNames
@TableNameD varchar(50),
@TableNameU varchar(50),
@TableUID int
--
--
AS
......
Insert into @TableNameD(ColumnName)
Select #TempColumnNames.Name
From #TempColumnNames
-----------------------------
I get "incorrect syntax near '@TableName'". (The input table does have a column called 'ColumnName'). How do you parametized an 'Insert Into' sql statement? I have tried a number of ways to no avail.
Thanks,
Judith
View 2 Replies
View Related
Dec 14, 2006
I cannot identify where the syntax error is, please help.
INSERT INTO [Attendence/Activity Log] (ID Number, Date, Activity, Duration) VALUES ('39', '12/14/06', 'Health & Nutrition', '2')
View 1 Replies
View Related
Aug 12, 2004
I have a insert statement but one of the strings contains a apostrophe. If I leave the apostrophe in an error occurs becuase it thinks that it is the end of the string. What is the proper syntax for including apostrophes in a string?
Thanks.
View 8 Replies
View Related
Sep 28, 2004
Hi,
I'm working in vb.net and want to use a stored procedure to insert all employees from one db into my db. I can insert one by one, but I would like to get them all in without looping.
How would I do this? I've tried bulk insert, but I keep getting syntax errors; I've read the books online, but don't quite understand what they mean. I don't want to use DTS, should I?
Here is what I'm doing so far:
CREATE Procedure Insert_From_Personnel
@emp_num char(10),
@Frst_Name char(10),
@Last_Name char(10),
@DivisionID char (4)
as
INSERT into individual (IndividualID,FirstName,LastName,DivisionID)
VALUES (@emp_num,@Frst_Name,@Last_Name,@DivisionID)
GO
Thanks for any help,
View 14 Replies
View Related
Apr 2, 2008
Hi,
I have a DTS package which, apart from other steps, loads a text file to the SQL Server (2000) table. The problem is that I need to do it for at least 20 text files, may be more.
As far as I have no experience in parametrizing DTS packages, I suppose it will be easier for me to do it with BULK INSERT.
What would be an equivalent BULK INSERT syntax for this load
(parameters taken from the DTS package mentioned)?
---------------------------------------
load a text file: path/txtfile.txt
(txtfile.txt on the network drive)
to an SQL Server 2000 table: db1.dbo.table1
Select File Format:
- Delimited
- File type: ANSI
- Row delimiter: Comma
- Text qualifier: Double Quote
- First row has column names: NO
Specify Column Delimiter:
- Tab
---------------------------------------
Thanks
Katarina
View 3 Replies
View Related
Sep 23, 2013
I just upsized my access backend and a few of my tables did not import data. I am trying to do a bulk insert and this is the code I am using:
"BULK INSERT [dbo].[invoices]"
Select ('VendorName, Invoice_Number, DueDate,Type_of_Invoice, Select_field, Invoice_Status, tday, discount, file_as, Invoice_amount, Date_Paid, check_number, apply_discount, amountpaid')
FROM 'A:Invoices2.csv'
;WITH
(
FIELDTERMINATOR = ',')',
ROWTERMINATOR = "n")',
GO
When I click on the execute button I get this message:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'A:Invoices2.csv'.
View 3 Replies
View Related
Feb 18, 2015
I create table with automatically generate the next ID.
create table #people
(id integer identity primary key not null,
name varchar(20),
surname varchar(30),
number Char(11)
);
and next I would like to add many lines
insert into #people (name, surname, number)
VALUES
('Anne', 'Ferguson', '123456789'),
('Eve', 'Atkinson', '234567891'),
('John', 'Smith','345678912');
and I've got:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ','.
This is something wrong with the row ('Anne...
I use sql MS server 2008
View 1 Replies
View Related
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
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
Dec 27, 2000
I'm trying something like this:
CREATE PROCEDURE Add_Junk @Dist char, @CheckNo int =null OUTPUT AS
Set NoCount On
BEGIN TRANSACTION
INSERT INTO Junk (Dist)
VALUES (@Dist)
COMMIT TRANSACTION
select @CheckNo=@@IDENTITY
If what I pass is "416" I only get the "4" in my database and nothing else.
I don't get an error message.
What is wrong with my syntax?
PS I'm using Microsoft SQL 7.0
View 2 Replies
View Related
Jun 13, 2004
The following code should insert into 3 tables based on conditions. There's something screwy in my syntax and I'm pretty new at this can anyone help with transforming this in terms of performance and being syntactically correct? Thanks a million!
CREATE PROCEDURE [insert_vwMusic]
(@Artist [nvarchar](50),
@Genre [nvarchar](50),
@NLink [nvarchar](50),
@Album[nvarchar](50),
@Song[nvarchar](50),
@ArtistID[nvarchar](50),
@AlbumID[nvarchar](50),
@SLink[nvarchar](50))
AS
DECLARE @NewArtistID VarChar(50),
DECLARE @NewAlbumID VarChar(50)
IF Not Exists (SELECT [Artist] FROM [integration].[dbo].[tblMusic_Artist] WHERE [Artist] = @Artist)
BEGIN
INSERT INTO [integration].[dbo].[tblMusic_Artist]
( [Artist],
[Genre],
[NLink])
VALUES
( @Artist,
@Genre,
@NLink)
SET @NewArtistID = @@IDENTITY
INSERT INTO [integration].[dbo].[tblMusic_Albums]
( [Album]
VALUES
( @Album)
SET @NewAlbumID = @@IDENTITY
INSERT INTO [integration].[dbo].[tblMusic_Song]
( [Song],
[ArtistID],
[AlbumID],
[SLink])
VALUES
( @Song,
@NewArtistID,
@NewAlbumID,
@SLink)
END
ELSE
BEGIN
IF Not Exists (SELECT [Album] FROM [integration].[dbo].[tblMusic_Album] WHERE [Album] = @Album)
BEGIN
INSERT INTO [integration].[dbo].[tblMusic_Albums]
( [Album]
VALUES
( @Album)
SET @NewAlbumID = @@IDENTITY
SET @NewArtistID = (SELECT [ID] FROM [integration].[dbo].[tblMusic_Artist] WHERE [Artist] = @Artist)
INSERT INTO [integration].[dbo].[tblMusic_Song]
( [Song],
[ArtistID],
[AlbumID],
[SLink])
VALUES
( @Song,
@NewArtistID,
@NewAlbumID,
@SLink)
END
END
ELSE
BEGIN
SET @NewAlbumID = (SELECT [ID] FROM [integration].[dbo].[tblMusic_Album] WHERE [Album] = @Album)
SET @NewArtistID = (SELECT [ID] FROM [integration].[dbo].[tblMusic_Artist] WHERE [Artist] = @Artist)
INSERT INTO [integration].[dbo].[tblMusic_Song]
( [Song],
[ArtistID],
[AlbumID],
[SLink])
VALUES
( @Song,
@NewArtistID,
@NewAlbumID,
@SLink)
END
GO
View 5 Replies
View Related
Sep 14, 2004
Hi all,
I have to insert records using transact iin the stored procedure. I have some thing like:
DECLARE @Err varchar(100)
DECLARE insertQ varchar(1000)
SET @Err = 'Insertion data'
SET @insertQ =('INSERT INTO dbo.T_ERRORLOG (ERROR_DESCR) VALUES(' + @Err + ')')
EXEC insertQ
But it don't work. Can you help me to solve the problem plz?
Thanks a lot
TT
View 4 Replies
View Related
Aug 9, 2006
May I know what does the syntax of inserting data into a field of type Unique Identifier look like?
[code]
INSERT INTO THAI_MK_MT_Log(GUID, Status) VALUES ('2331486348632', 'S')
[/code]
The "2331486348632" is to be inserted into a unique identifier field.
If i coded the insert statement as the above, I got an error saying that "Syntax error converting from a character string to uniqueidentifier".....
Can anyone help?
View 11 Replies
View Related
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
Aug 28, 2014
I was trying to figure out what the OPENQUERY Syntax is to Insert into SQL Server Table from Oracle Linked Server.
View 7 Replies
View Related
May 20, 2008
Why does the following call to a stored procedure get me this error:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'CONVERT'.
Code Snippet
EXECUTE OpenInvoiceItemSP_RAM CONVERT(DATETIME,'01-01-2008'), CONVERT(DATETIME,'04/30/2008') , 1,'81350'
The stored procedure accepts two datetime parameters, followed by an INT and a varchar(10) in that order.
I can't find anything wrong in the syntax for CONVERT or any nearby items.
Help me please. Thank you.
View 7 Replies
View Related
Dec 14, 2003
I keep receiving the following error whenever I try and call this function to update my database.
The code was working before, all I added was an extra field to update.
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'WHERE'
Public Sub MasterList_Update(sender As Object, e As DataListCommandEventArgs)
Dim strProjectName, txtProjectDescription, intProjectID, strProjectState as String
Dim intEstDuration, dtmCreationDate, strCreatedBy, strProjectLead, dtmEstCompletionDate as String
strProjectName = CType(e.Item.FindControl("txtProjectName"), TextBox).Text
txtProjectDescription = CType(e.Item.FindControl("txtProjDesc"), TextBox).Text
strProjectState = CType(e.Item.FindControl("txtStatus"), TextBox).Text
intEstDuration = CType(e.Item.FindControl("txtDuration"), TextBox).Text
dtmCreationDate = CType(e.Item.FindControl("txtCreation"),TextBox).Text
strCreatedBy = CType(e.Item.FindControl("txtCreatedBy"),TextBox).Text
strProjectLead = CType(e.Item.FindControl("txtLead"),TextBox).Text
dtmEstCompletionDate = CType(e.Item.FindControl("txtComDate"),TextBox).Text
intProjectID = CType(e.Item.FindControl("lblProjectID"), Label).Text
Dim strSQL As String
strSQL = "Update tblProject " _
& "Set strProjectName = @strProjectName, " _
& "txtProjectDescription = @txtProjectDescription, " _
& "strProjectState = @strProjectState, " _
& "intEstDuration = @intEstDuration, " _
& "dtmCreationDate = @dtmCreationDate, " _
& "strCreatedBy = @strCreatedBy, " _
& "strProjectLead = @strProjectLead, " _
& "dtmEstCompletionDate = @dtmEstCompletionDate, " _
& "WHERE intProjectID = @intProjectID"
Dim myConnection As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("connectionstring"))
Dim cmdSQL As New SqlCommand(strSQL, myConnection)
cmdSQL.Parameters.Add(new SqlParameter("@strProjectName", SqlDbType.NVarChar, 40))
cmdSQL.Parameters("@strProjectName").Value = strProjectName
cmdSQL.Parameters.Add(new SqlParameter("@txtProjectDescription", SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@txtProjectDescription").Value = txtProjectDescription
cmdSQL.Parameters.Add(new SqlParameter("@strProjectState", SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@strProjectState").Value = strProjectState
cmdSQL.Parameters.Add(new SqlParameter("@intEstDuration", SqlDbType.NVarChar, 60))
cmdSQL.Parameters("@intEstDuration").Value = intEstDuration
cmdSQL.Parameters.Add(new SqlParameter("@dtmCreationDate", SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@dtmCreationDate").Value = dtmCreationDate
cmdSQL.Parameters.Add(new SqlParameter("@strCreatedBy", SqlDbType.NVarChar, 10))
cmdSQL.Parameters("@strCreatedBy").Value = strCreatedBy
cmdSQL.Parameters.Add(new SqlParameter("@strProjectLead", SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@strProjectLead").Value = strProjectLead
cmdSQL.Parameters.Add(new SqlParameter("@dtmEstCompletionDate", SqlDbType.NVarChar, 24))
cmdSQL.Parameters("@dtmEstCompletionDate").Value = dtmEstCompletionDate
cmdSQL.Parameters.Add(new SqlParameter("@intProjectID", SqlDbType.NChar, 5))
cmdSQL.Parameters("@intProjectID").Value = intProjectID
myConnection.Open()
cmdSQL.ExecuteNonQuery
myConnection.Close()
MasterList.EditItemIndex = -1
BindMasterList()
End Sub
Thankyou in advance.
View 3 Replies
View Related
Mar 31, 2008
Forgive the noob question, but i'm still learning SQL everyday and was wondering which of the following is faster? I'm just gonna post parts of the SELECT statement that i've made changes to:
INNER JOIN Facilities f ON e.Facility = f.FacilityID AND f.Name = @FacilityName
OR
WHERE f.Name = @FacilityName
My question is whether or not the query runs faster if i put the condition within the JOIN line as opposed to putting in the WHERE line? Both ways seems to return the same results but the time difference between methods is staggering? Putting the condition within the JOIN line makes the query run about 3 times faster?
Again, forgive my lack of understanding, but could someone agree or disagree and give me the cliff-notes version of why or why not?
Thanks!
View 4 Replies
View Related
Sep 23, 2007
Ok I am tying to convert access syntax to Sql syntax to put it in a stored procedure or view..
Here is the part that I need to convert:
SELECT [2007_hours].proj_name, [2007_hours].task_name, [2007_hours].Employee,
IIf(Mid([task_name],1,3)='PTO','PTO_Holiday',
IIf(Mid([task_name],1,7)='Holiday','PTO_Holiday',
IIf(Mid([proj_name],1,9) In ('9900-2831','9900-2788'),'II Internal',
IIf(Mid([proj_name],1,9)='9900-2787','Sales',
IIf(Mid([proj_name],1,9)='9910-2799','Sales',
IIf(Mid([proj_name],1,9)='9920-2791','Sales',
)
)
)
)
) AS timeType, Sum([2007_hours].Hours) AS SumOfHours
from................
how can you convert it to sql syntax
I need to have a nested If statment which I can't do in sql (in sql I have to have select and from Together for example ( I can't do this in sql):
select ID, FName, LName
if(SUBSTRING(FirstName, 1, 4)= 'Mike')
Begin
Replace(FirstNam,'Mike','MikeTest')
if(SUBSTRING(LastName, 1, 4)= 'Kong')
Begin
Replace(LastNam,'Kong,'KongTest')
if(SUBSTRING(Address, 1, 4)= '1245')
Begin
.........
End
End
end
Case Statement might be the solution but i could not do it.
Your input will be appreciated
Thank you
View 5 Replies
View Related
May 27, 2008
This is the error it gives me for my code and then it calls out line 102. Line 102 is my buildDD(sql, ddlPernames) When I comment out this line the error goes away, but what I don't get is this is the same way I build all of my dropdown boxes and they all work but this one. Could it not like something in my sql select statement. thanksPrivate Sub DDLUIC_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DDLUIC.SelectedIndexChanged
Dim taskforceID As Byte = ddlTaskForce.SelectedValueDim uic As String = DDLUIC.SelectedValue
sql = "select sidstrNAME_IND from CMS.dbo.tblSIDPERS where sidstrSSN_SM in (Select Case u.strSSN from tblAssignedPersonnel as u " _
& "where u.bitPresent = 1 and u.intUICID in (select intUICID from tblUIC where intTaskForceID = " & taskforceID & " and strUIC = '" & uic & "'))"ddlPerNames.Items.Add(New ListItem("", "0"))
buildDD(sql, ddlPerNames)
End Sub
View 2 Replies
View Related
Jun 4, 2008
hello friends
my one insert code lines is below :) what does int32 mean ? AND WHAT IS DIFFERENT BETWEEN ONE CODE LINES AND SECOND CODE LINES :)Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
Dim cmd As New SqlCommand("Insert into table1 (UserId) VALUES (@UserId)", conn)
'you should use sproc instead
cmd.Parameters.AddWithValue("@UserId", textbox1.text)
'your value
Try
conn.Open()Dim rows As Int32 = cmd.ExecuteNonQuery()
conn.Close()Trace.Write(String.Format("You have {0} rows inserted successfully!", rows.ToString()))
Catch sex As SqlExceptionThrow sex
Finally
If conn.State <> Data.ConnectionState.Closed Then
conn.Close()
End If
End Try
MY SECOND INSERT CODE LINES IS BELOWDim SglDataSource2, yeni As New SqlDataSource()
SglDataSource2.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionString").ToString
SglDataSource2.InsertCommandType = SqlDataSourceCommandType.Text
SglDataSource2.InsertCommand = "INSERT INTO urunlistesi2 (kategori1) VALUES (@kategori1)"
SglDataSource2.InsertParameters.Add("kategori1", kategoril1.Text)Dim rowsaffected As Integer = 0
Try
rowsaffected = SglDataSource2.Insert()Catch ex As Exception
Server.Transfer("yardim.aspx")
Finally
SglDataSource2 = Nothing
End Try
If rowsaffected <> 1 ThenServer.Transfer("yardim.aspx")
ElseServer.Transfer("urunsat.aspx")
End If
cheers
View 2 Replies
View Related
