Help With Inserting Array Contents To SQL Server 2000
Jan 29, 2007
I've been doing this in Access, but cannot find the answer to how to do it with SQL Server.
From a web form, a user can select a number of different dates. The selected dates are held as text (not DateTime) in an ArrayList.
Clicking the Submit button writes the contents of the form to a database table.
This works for Access:
insSQL &= "VALUES (@typEvent, @starts, @ends, @starts, @ends, @attend, @title, @room, @department, @contact, @address, @telephone, @email, @telefax, "
For i = 0 to datesArray.Count - 1
insSql &= datesArray.Item(i)
Next i
insSQL &= "VALUES (@typEvent, @starts, @ends, @starts, @ends, @attend, @title, @room, @department, @contact, @address, @telephone, @email, @telefax, "
For i = 0 to datesArray.Count - 1
insSql &= "#" & datesArray.Item(i) & "#, "
Next i
It doesn't work for SQL Server, and when trying to insert the value "01/29/2007" I get the error message: "The name '#1' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted."
I have also tried the line:
For i = 0 to datesArray.Count - 1
insSql &= satesArray.Item(i)
Next i
and get: "Incorrect syntax near the keyword 'VALUES'."
I'm not sure where to find the information to correct my error.
SET @MyCursor = CURSOR FAST_FORWARD FOR Select nome_consigliere From t_immoconsiglieri where immobile_id=@immobile_id order by posizione OPEN @MyCursor FETCH NEXT FROM @MyCursor INTO @ColA WHILE @@FETCH_STATUS = 0 BEGIN
Insert @tbl SELECT dbo.T_Condomini.Condomino_id,dbo.T_Condomini.titolo,dbo.T_Condomini.Nominativo,dbo.T_UniCond.StCon_id FROM dbo.T_Condomini INNER JOIN dbo.T_UniCond ON dbo.T_Condomini.Condomino_id = dbo.T_UniCond.Condomino_id INNER JOIN dbo.T_Unita ON dbo.T_UniCond.Unita_id = dbo.T_Unita.Unita_id WHERE (dbo.T_Condomini.Nominativo = @ColA) AND (dbo.T_UniCond.Dta_fine = '21001231') AND (dbo.T_Unita.Immobile_id =@immobile_id) and dbo.T_UniCond.StCon_id<>3 FETCH NEXT FROM @MyCursor INTO @ColA END
CLOSE @MyCursor DEALLOCATE @MyCursor
select * from @tbl
/*SET QUOTED_IDENTIFIER OFF*/ GO , When i call store procedure with vb6 Dim rs as new adodb.recordset Set cmd = New ADODB.Command Dim pm As New ADODB.Parameter ' conn.BeginTrans Set cmd.ActiveConnection = conn cmd.CommandType = adCmdStoredProc Set pm = cmd.CreateParameter("immobile_id", adVarChar, adParamInput, 6, immobile_id) cmd.Parameters.Append pm cmd.CommandText = "sptcondconsiglieri" Set rs = cmd.Execute If Not rs.EOF Then ' Rs is close I dont undestand why Tank you
Hi, I was wondering if there is a method (other than BULK INSERT) to insert a (C++) application level array into the database, I have a variant type array populated with values that I want to insert, possibly using ADO Objects, in quick time!
I have an asp.net project that displays a timesheet based on a fortnightly system.
It has a Y and X axis, i.e. Sun-Mon, Sun-Mon across the top and Categories of hour types accross the Y axis, i.e. Holidays, Overtime, Maintenance.
I was using a datagrid to gather the rows, but I need to have the whole grid in edit mode which is something that requires more coding. So I have swapped to a datalist.
It will take me 240 fields to store all the data in their corresponding fields. So I basically need to know if there is an easier way to store two dimensional array type data into an SQL table using a minimum of fields.
Hi All,I have come up against a wall which i cannot get over.I have an sql db where the date column is set as a varchar (i know, should have used datetime but this was done before my time and i've got to work with what is there). The majority of values are in the format dd/mm/yyyy. However, some values contain the word 'various'.I'm attempting to compare the date chosen on a c# .net page with the values in the db and also return all the 'various' values as well.I have accomplished casting the varchar to a datetime and then comparing to the selected date on the .net page. However, it errors when it comes across the 'various' entrant.Is there anyway to carry out a select statement comparing the start_date values in the db to the selected date on the .net page and also pull out all 'various' entrants at the same time without it erroring? i thought about replacing the 'various' to a date like '01/01/2010' so it doesn't stumble over the none recognised format, but am unsure of how to do it.This is how far i have got: casting the varchar column to datetime and comparing. SELECT * FROM table1 WHERE Cast(SUBSTRING(Start_Date,4,2) + '/' + SUBSTRING(Start_Date,1,2) + '/' +SUBSTRING(Start_Date,7,4) as datetime) '" + date + "'"Many thanks in advance!
In my VS 2005 windows control I am inserting a record into a table using a proc. One of the fields "Accuracy" should look like this 66.4, but when I isnert it from the proc itlooks like 66.0. If I bypass my proc and use an inser statement from SQL quey analyzer it look like it should 66.4. What am I doing wrong in my proc....
CREATE PROCEDURE [dbo].[insMyLameProc] @PlayerName nvarchar(255),@Score int,@Rounds int,@Accuracy decimal,@CorrectPicks int,@IncorrectPicks int AS --Insert the new game score--===============================================================================================insert into wmTurnTileScores(PlayerName, Score, Rounds, Accuracy, CorrectPicks, IncorrectPicks) values (@PlayerName, @Score, @Rounds, @Accuracy, @CorrectPicks, @IncorrectPicks)--===============================================================================================GO
This is my code: Dim myConn As SqlConnection Dim mycmd As SqlCommand myConn = New SqlConnection("Initial Catalog=science;" & _ "Data Source=localhost;Integrated Security=SSPI;") mycmd = New SqlCommand("INSERT into STEP1(firstname) VALUES('Amin')", myConn) myConn.Open() mycmd.ExecuteNonQuery() myConn.Close()
This is the error message I get: The name 'firstname' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.
Edited by SomeNewKid. Please post code between <code> and </code> tags.
This is probaly the easiest question you've ever read but here goes.
I have a simple checkbox value that i want to insert into the database but whatever i do it does not seem to let me.
Here is my code:
Sub AddSection_Click(Sender As Object, e As EventArgs) Dim myCommand As SqlCommand Dim insertCmd As String ' Build a SQL INSERT statement string for all the input-form ' field values. insertCmd = "insert into Customers values (@SectionName, @SectionLink, @Title, @NewWindow, @LatestNews, @Partners, @Support);" ' Initialize the SqlCommand with the new SQL string. myCommand = New SqlCommand(insertCmd, myConnection) ' Create new parameters for the SqlCommand object and ' initialize them to the input-form field values. myCommand.Parameters.Add(New SqlParameter("@SectionName", SqlDbType.nVarChar, 50)) myCommand.Parameters("@SectionName").Value = Section_name.Value
If New_window.Checked = false Then myCommand.Parameters.Add(New SqlParameter("@NewWindow", SqlDbType.bit, 1)) myCommand.Parameters("@NewWindow").Value = 0 else myCommand.Parameters.Add(New SqlParameter("@NewWindow", SqlDbType.bit, 1)) myCommand.Parameters("@NewWindow").Value = 1 End If
If Latest_news.Checked = false Then myCommand.Parameters.Add(New SqlParameter("@LatestNews", SqlDbType.bit, 1)) myCommand.Parameters("@LatestNews").Value = 0 else myCommand.Parameters.Add(New SqlParameter("@LatestNews", SqlDbType.bit, 1)) myCommand.Parameters("@LatestNews").Value = 1 End If
If Partners.Checked = false Then myCommand.Parameters.Add(New SqlParameter("@Partners", SqlDbType.bit, 1)) myCommand.Parameters("@Partners").Value = 0 else myCommand.Parameters.Add(New SqlParameter("@Partners", SqlDbType.bit, 1)) myCommand.Parameters("@Partners").Value = 1 End If
If Support.Checked = false Then myCommand.Parameters.Add(New SqlParameter("@Support", SqlDbType.bit, 1)) myCommand.Parameters("@Support").Value = 0 else myCommand.Parameters.Add(New SqlParameter("@Support", SqlDbType.bit, 1)) myCommand.Parameters("@Support").Value = 1 End If
myCommand.Connection.Open() ' Test whether the new row can be added and display the ' appropriate message box to the user. Try myCommand.ExecuteNonQuery() Message.InnerHtml = "Record Added<br>" & insertCmd Catch ex As SqlException If ex.Number = 2627 Then Message.InnerHtml = "ERROR: A record already exists with " _ & "the same primary key" Else Message.InnerHtml = "ERROR: Could not add record, please " _ & "ensure the fields are correctly filled out" Message.Style("color") = "red" End If End Try
I am trying to insert quoted strings into a database table, however, I cannot remember how to do so. For instance, I am trying to insert the following values into a SQL table:
My Friend's "Happy Birthday"
exactly as they are listed. How can I do that in a SQL insert statement?
I am manually replicating parts of a SQL Server CE database (running windows mobile 5.0) to a centralized SQL Server 2000 database.
My program is throwing an exception whenever I try to insert an image data type into the 2000 server from the PDA. I am using parameterized queries.
Error is as follows: [error] System.Data.SqlClient.SqlConnection.OnError() at System.Data.SqlClient.SqlInternalConnection.OnError() at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at System.Data.SqlClient.TdsParser.Run() at System.Data.SqlClient.ExecuteReader() at System.Data.SqlClient.ExecuteNonQuery() at PDASync.Database.ExecuteIDRemote() [/error]
The code for my ExecuteIDRemote method works fine for other queries. It also works if I remove the image column from the offending query.
Hi, I would preciated if some could help me with this problem. My have used Mysql database and now i have to change my database to sql server. I have made a dump file from mysql db and i would like to insert that dumb file to sql server. Have any idea how it is possible?
Hi All, I am working on SQL Server 2000 ver 7.0. The Collation set for my Database Server is Latin. I want some way by which i can insert Japanese Characters in Database. Is it related to change the Collation or any other encoding format of database. Suppose the table 'Person' has fields id, Name, city If i enter name in a japanese characters, then while storing it does not recongnises this format.
insert into person values(8,'満員','osaka')
id name city 8 ?? osaka At the place of name '??' is displayed.
we have tables with many image columns. We fill these image columns via ODBC and SQLPutData as described in MSDN etc (using SQL_LEN_DATA_AT_EXEC(...), calling SQLParamData and sending the data in chunks of 4096 bytes when receiving SQL_NEED_DATA).
The SQLPutData call fails under the following conditions with sqlstate 08S01
- The database resides on SQL Server 2000 - The driver is SQL Native Client - The table consists e.g. of one Identity column (key column) and nine image columns - The data to be inserted are nine blocks of data with the following byte size:
1: 6781262 2: 119454
3: 269 4: 7611
5: 120054
6: 269
7: 8172
8: 120054
9: 269 The content of the data does not matter, (it happens also if only zero bytes are written), nor does the data origin (file or memory).
All data blocks including no 7 are inserted. If the first chunk of data block 8 should be written with SQLPutData the function fails and the connection is broken. There are errors such as "broken pipe" or "I/O error" depending on the used network protocol.
If data no 7 consists of 8173 bytes instead of 8172 all works again. (Changing the 4096 chunk size length does not help)
Has anybody encountered this or a similar phenomenon?
I have a stored procedure that has a paramter that accepts a string of values. At the user interface, I use a StringBuilder to concatenate the values (2,4,34,35,etc.) I would send these value to the stored procedure. The problem is that the stored procedure doesn't allow it to be query with the parameter because the Fieldname, "Officer_UID" is an integer data type, which can't be query against parameter string type. What would I need to do to convert it to an Integer array? @OfficerIDs as varchar(200) Select Officer_UID From Officers Where Officer_UID in (@OfficerIDs) Thanks
Hello, I am working on some maintenance work. I need a Stored procedure to read all contents of OS and SQL server log. It has to accept filename as IN parameter. Please help me to solve this problem.
hello, I am working on some maintenance process. I need a Stored Procedure to reda the contents of a file. (i.e. OS or SQL server log files). plz help me in sort out this problem.
i'm an sql server beginer. i was wondering if some of you guys can help me out. i need for the sql server to be able to read an outside file (just text) and be able to run a script that will insert it in the database. it's a dcc output file. we've tried running this script:
DROP TABLE tests go DECLARE @SQLSTR varchar(255) SELECT @SQLSTR = 'ISQL -E -Q"dbcc checkdb(master)"' CREATE TABLE tests (Results varchar(255) NOT NULL) INSERT INTO tests EXEC('master..xp_cmdshell ''ISQL -E -Q"dbcc checkdb(master)"''')
and it's running good but the problem is the results of the dbcc here did not come from a file but directly after executing the dcc command. is there a way to do it?
I have a large table containing about 800 million rows with an average row length of about 1K. The columns in the table are char columns. I need to move the contents of this table into a similar table where the target columns are varchar. The original table column definitions are compatible with the target table but the reverse is not necessarily true. For example, one column is being changed from int to bigint. The table is partitioned.
So, what is the fastest way to migrate the data. I was thinking to unload each partition into a flat file and load the target table running multiple load streams? Is this a good way?
I am just learning about temp temps using Iteration, how do I clear the contents of the #temp table because when I re-run the query I get the following error:
Msg 2714, Level 16, State 6, Line 6
There is already an object named '#mytemp2' in the database.
Is there an easy way to compare the contents of objects between 2 different databases? For example, say I had 2 databases, My_DB_1 and My_DB_2, each with a SEC_User table. Say I wanted to do an object-to-object comparison between databases to see if there were any differences. Here's some sample SQL:
use My_DB_1 select * from sys.dm_sql_referencing_entities('dbo.sec_user', 'object')
use My_DB_2 select * from sys.dm_sql_referencing_entities('dbo.sec_user', 'object')
Say that the result sets above both returned a SEC_GetUser sproc object ref. Is there a way to write SQL that will compare the SEC_GetUser sprocs (and other objects in the above rowsets) from both databases? For example, if My_DB_1.SEC_GetUser returns an extra column in the result set than My_DB_2.SEC_GetUser then I'd like my comparison SQL to return a single column "IsDifferent" with SEC_GetUser as a row....
I have a requirement to create a package that takes all files in a given folder and adds them to a single archive (.zip) file. I've tried several methods using 7zip and while I can create archives for every file in the directory I can't seem to get them into a single .zip file.
I have been trying to add values to a database and it keeps failing i have no idea what i am doing wrong please help the code is asp.net using vb. I have been having serious trouble passing check boxes in forms from day one both singularly and dynamically from datagrids if someone could show me some sample code of how to pass these sort of values into the component and on to the query in this way i would very much appreciate it.
Fuzzygoth
the error returned is
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: INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'tblStation_FK00'. The conflict occurred in database 'TealSQL', table 'tblTravelPoint', column 'travelpointID'. The statement has been terminated.
I have marked the area of the code the error is returned in the colour Violet and with a ##
The code i am using is below
## The aspx page ##
<%@ Page Language="vb" Debug="true" Trace="True" Inherits="Devotion2Motion.AdminComp" Src="../CodeBehind/AdminModule.vb" %> <!-- Binds the ActivityResortInfo.ascx user control to the page --> <%@ Register TagPrefix="UserContol" TagName="D2MHeader" Src="../UserControls/Header.ascx" %> <%@ Register TagPrefix="UserContol" TagName="D2MFooter" Src="../UserControls/Footer.ascx" %> <%@ Register TagPrefix="UserContol" TagName="TravelPointDD" Src="../UserControls/TravelPointDD.ascx" %> <script language="vb" runat="server">
Sub Page_Load()
If IsPostback = True Then
Dim aInternational As Integer
Dim Station As String = Request.Form("StationFrm") Dim Type As String = Request.Form("TypeFrm") Dim Address1 As String = Request.Form("address1Frm") Dim Address2 As String = Request.Form("address2Frm") Dim City As String = Request.Form("cityFrm") Dim International As String = Request.Form("InternationalFrm") Dim TravelPoint As Integer = Request.Form("_ctl5:dsTravelPointDD")
If IsNothing(International) Then aInternational = "0" Else aInternational = "1" End If
Dim AdminTravelPoints As New Devotion2Motion.AdminComp() ' Select the country dropdown list
AdminTravelPoints.AddStation(Station, Type, Address1, Address2, City, aInternational, TravelPoint)
End If
Dim ReadResultTable As New Devotion2Motion.AdminComp() ' Select the country dropdown list dsResultSet.DataSource = ReadResultTable.GetStationtbl() dsResultSet.DataBind()
End Sub
</script>
<!-- This UserControl Pulls in the header UserControl and the Div Tag Positions it #css reffrence is TopControl --> <Div Class="TopControl"> <UserContol:D2MHeader runat="server"/> </Div>
##the vb componet that passess to the sql query ##
Public Function AddStation(ByVal Station As String, ByVal Type As String, ByVal Address1 As String, ByVal Address2 As String, ByVal City As String, ByVal aInternational As Integer, ByVal TravelPoint As Integer) As SqlDataReader
' Create Instance of Connection and Command Object Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("strConn")) Dim myCommand As New SqlCommand("sp_call_Station_Insert", myConnection)
' Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure
' Add Parameters to SPROC Dim parameterStation As New SqlParameter("@Station", SqlDbType.NVarChar, 50) parameterStation.Value = Station myCommand.Parameters.Add(parameterStation)
' Add Parameters to SPROC Dim parameterType As New SqlParameter("@Type", SqlDbType.NVarChar, 50) parameterType.Value = Type myCommand.Parameters.Add(parameterType)
' Add Parameters to SPROC Dim parameterAddress1 As New SqlParameter("@Address1", SqlDbType.NVarChar, 50) parameterAddress1.Value = Address1 myCommand.Parameters.Add(parameterAddress1)
' Add Parameters to SPROC Dim parameterAddress2 As New SqlParameter("@Address2", SqlDbType.NVarChar, 50) parameterAddress2.Value = Address2 myCommand.Parameters.Add(parameterAddress2)
' Add Parameters to SPROC Dim parameterCity As New SqlParameter("@City", SqlDbType.NVarChar, 50) parameterCity.Value = City myCommand.Parameters.Add(parameterCity)
' Add Parameters to SPROC Dim parameteraInternational As New SqlParameter("@aInternational", SqlDbType.Int, 4) parameteraInternational.Value = aInternational myCommand.Parameters.Add(parameteraInternational)
' Add Parameters to SPROC Dim parameterTravelPoint As New SqlParameter("@TravelPoint", SqlDbType.Int, 4) parameterTravelPoint.Value = TravelPoint myCommand.Parameters.Add(parameterTravelPoint)
' Execute the command myConnection.Open()
## Dim result As SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
' Return the datareader Return result
End Function
## The sql stored procedure ##
CREATE PROCEDURE [dbo].[sp_call_Station_Insert] ( @Station As nVarChar(50), @Type As nVarChar(50), @address1 As nVarChar(50), @address2 As nVarChar(50), @City As nVarChar(50), @aInternational As nVarChar(50), @TravelPoint As Int ) AS
DECLARE @ConVale As nVarChar(50)
SET @ConVale = (SELECT Station FROM tblStation WHERE @Station = Station)
If @ConVale = @Station
BEGIN SELECT * FROM tblStation END ELSE BEGIN insert into tblStation (Station, Type, address1, address2, city, International, TravelPoint) values (@Station, @Type, @address1, @address2, @City, @aInternational, @TravelPoint) SELECT * FROM tblStation
Hello.I've read many topics about this problem but i couldn't figure it out.I use form where user must insert 2 dates using texboxes.-One is required and other is optional.Sql 2000 is inserting either '20061105' or '2006.11.05' on insert update but select query returns 05.11.2006 on my report. Question 1.How do I insert or update dates from my form where date is entered dd.mm.yyyy to sql 2000 table?question 2. What to do if user left optional texbox date empty.I'm using SP and function with arguments (byval texbox1.text as date, byval texbox2.text as date)and parameters @date1, sqldbtype date =texbox1.text
To anyone that is able to help....What I am trying to do is this. I have two tables (Orders, andOrderDetails), and my question is on the order details. I would liketo set up a stored procedure that essentially inserts in the orderstable the mail order, and then insert multiple orderdetails within thesame transaction. I also need to do this via SQL 2000. Right now ihave "x" amount of variables for all columns in my orders tables, andall Columns in my Order Details table. I.e. @OColumn1, @OColumn2,@OColumn3, @ODColumn1, @ODColumn2, etc... I would like to create astored procedure to insert into Orders, and have that call anotherstored procedure to insert all the Order details associated with thatorder. The only way I can think of doing it is for the program to passme a string of data per column for order details, and parse the stringvia T-SQL. I would like to get away from the String format, and gowith something else. If possible I would like the application tosubmit a single value per variable multiple times. If I do it this waythough it will be running the entire SP again, and again. Anysuggestions on the best way to solve this would be greatlyappreciated. If anyone can come up with a better way feel free. Myonly requirement is that it be done in SQL.Thank you
Hi All,I want declare an array of int in SQL Serverplease help me to convert the following code from VB to TSQL------------Dim md_mon(12) As Integermd_mon(1) = 31-------------Thanks
