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) )
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
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?
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.
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?
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
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
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>
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="Addressbox" ErrorMessage="Please Enter An Address"></asp:RequiredFieldValidator>
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.
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
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?
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?
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
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
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.
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?
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!
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
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".....
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!
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
Dim myConnection As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("connectionstring")) Dim cmdSQL As New SqlCommand(strSQL, myConnection)
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?
) ) ) ) ) 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.
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)
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
