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.
Any help would be appreciated.
Tinker
View 3 Replies
ADVERTISEMENT
May 11, 2006
hello
I have created one store procedures that return a table variable
'CREATE PROCEDURE sptcondconsiglieri @immobile_id varchar(6)
as
DECLARE @tbl table(condomio_id Varchar(6),titlo varchar(5),nominativo varchar(256),stato int)
DECLARE @colA nvarchar(50)
DECLARE @MyCursor CURSOR
/*declare @mycursor1 cursor*/
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
View 5 Replies
View Related
Feb 13, 2008
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!
View 1 Replies
View Related
Dec 23, 2003
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.
View 1 Replies
View Related
Jun 25, 2007
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!
View 7 Replies
View Related
Aug 19, 2007
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
View 3 Replies
View Related
Mar 21, 2004
Tryinna insert a new row
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.
View 9 Replies
View Related
Dec 17, 2004
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
myCommand.Parameters.Add(New SqlParameter("@SectionLink", SqlDbType.nVarChar, 80))
myCommand.Parameters("@SectionLink").Value = Section_link.Value
myCommand.Parameters.Add(New SqlParameter("@Title", SqlDbType.nVarChar, 50))
myCommand.Parameters("@Title").Value = Section_title.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
myCommand.Connection.Close()
BindGrid()
End Sub
Any response would be appreciated
View 2 Replies
View Related
Jul 27, 2006
Hello,
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?
Thanks,
Crystal
View 1 Replies
View Related
Oct 23, 2006
Hi All,
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.
Does anyone have any suggestions? Thanks.
View 1 Replies
View Related
Apr 14, 2004
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?
cheers,
inkku
View 1 Replies
View Related
Apr 19, 2006
Hi Folks,
I was wondering if someone could give some advice on the best way to insert a file to nvarbinary column.
Currently, I have a table like so:
FileID (bigInt, 8)
Name (nvarchar, 255)
File (nvarbinary, 8000)
I would like to insert a file into the file column.
The problem is reading the file from the filesystem into the database.Could anyone give me some advice on the best way to do this?
Many thanks in advance,
Morris
View 1 Replies
View Related
Oct 15, 2007
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.
Please sugggest me a solution for this.
Thanks in Advance Manohar
View 11 Replies
View Related
Mar 31, 2006
Any one know about it?
View 9 Replies
View Related
May 28, 2008
Dear all,
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?
Thank you
Eartha
View 7 Replies
View Related
Jan 25, 2007
All,
I am using Reporting Services 2005. One of my reports is getting the following error when I try to export to Excel. It will export to .CSV though.
"Destination array was not long enough. Check destIndex and length, and the array's lower bounds."
Any suggestions would be greatly appreciated. Please copy me at machelle.a.chandler@intel.com.
Machelle
View 10 Replies
View Related
Jun 25, 2007
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
View 5 Replies
View Related
Jun 11, 2002
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.
regards,
vnk.
View 3 Replies
View Related
Aug 17, 2000
What is the best way to read and edit data in the tables of a sql server 6.5 database?
Thanks
Gunnar
gunnardl@yahoo.com
View 1 Replies
View Related
Jun 11, 2002
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.
regards,
venkat.
View 2 Replies
View Related
Aug 8, 2000
hello!
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?
thank's for the help!
View 1 Replies
View Related
Mar 3, 2015
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?
View 0 Replies
View Related
Jun 6, 2015
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.
View 2 Replies
View Related
Jul 27, 2015
who i can to list the content of all store procedures and funtions in text file ?
View 3 Replies
View Related
Sep 26, 2013
this directory is over 2gb
Program FilesMicrosoft SQL Server100Setup BootstrapUpdate Cache
can you delete the contents safely?
View 1 Replies
View Related
Jul 20, 2015
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....
View 1 Replies
View Related
Aug 12, 2015
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.
View 9 Replies
View Related
Jan 27, 2004
How would one implement an array in SQL Server ?
Specically, i'm parsing a field into the array, and doing operations on these elements.
Thanks.
View 4 Replies
View Related
Sep 12, 2005
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>
<form method="post" action="AdminTravelPoint.aspx" runat="server">
<Div Class="CentreControl">
<table border="0">
<tr>
<td>
<asp:Label Text="Station Name: " CssClass="BodyStyle" runat="server"/>
</td>
<td>
<asp:TextBox ID="StationFrm" CssClass="PrimaryGrid" runat="server"/>
</td>
<tr>
<tr>
<td>
<asp:Label Text="Type: " CssClass="BodyStyle" runat="server"/>
</td>
<td>
<asp:TextBox ID="TypeFrm" CssClass="PrimaryGrid" runat="server"/>
</td>
<tr>
<tr>
<td>
<asp:Label Text="Address1: " CssClass="BodyStyle" runat="server"/>
</td>
<td>
<asp:TextBox ID="Address1Frm" CssClass="PrimaryGrid" runat="server"/>
</td>
<tr>
<tr>
<td>
<asp:Label Text="Address2: " CssClass="BodyStyle" runat="server"/>
</td>
<td>
<asp:TextBox ID="Address2Frm" CssClass="PrimaryGrid" runat="server"/>
</td>
<tr>
<tr>
<td>
<asp:Label Text="City: " CssClass="BodyStyle" runat="server"/>
</td>
<td>
<asp:TextBox ID="CityFrm" CssClass="PrimaryGrid" runat="server"/>
</td>
<tr>
<tr>
<td>
<asp:Label Text="International: " CssClass="BodyStyle" runat="server"/>
</td>
<td>
<asp:CheckBox ID="InternationalFrm" CssClass="PrimaryGrid" runat="server"/>
</td>
<tr>
</tr>
<td>
<asp:Label Text="Travel Point: " CssClass="BodyStyle" runat="server"/>
</td>
<td>
<UserContol:TravelPointDD runat="server"/>
</td>
<tr>
</tr>
<td>
</td>
<td>
<input value="Submit" type="submit">
</td>
</tr>
</table>
</Div>
<asp:DataGrid ID="dsResultSet" AutoGenerateColumns="False" CssClass="PrimaryGrid" ShowHeader="true" runat="server">
<Columns>
<asp:BoundColumn DataField="StationID" ReadOnly="true" HeaderText="Station ID"/>
<asp:BoundColumn DataField="Station" ReadOnly="true" HeaderText="Station"/>
<asp:BoundColumn DataField="Type" ReadOnly="true" HeaderText="Type"/>
<asp:BoundColumn DataField="Address1" ReadOnly="true" HeaderText="Address"/>
<asp:BoundColumn DataField="Address2" ReadOnly="true" HeaderText=" "/>
<asp:BoundColumn DataField="City" ReadOnly="true" HeaderText="City"/>
<asp:BoundColumn DataField="International" ReadOnly="true" HeaderText="International"/>
<asp:BoundColumn DataField="TravelPoint" ReadOnly="true" HeaderText="City"/>
</Columns>
</asp:DataGrid>
</form>
<Div Class="CenterControl">
<UserContol:D2MFooter 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
END
GO
View 1 Replies
View Related
Apr 12, 2006
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
View 2 Replies
View Related
Jul 20, 2005
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
View 3 Replies
View Related
Jun 30, 2000
I heard from my friend that, in "Oracle 8i", they have Array concepts.
Is there any "ARRAY CONCEPTS" available in SQL Server 7.0?. I have seen in BOL, there they have given command like,
"SetToArray(«Set»[, «Set»]...[, «Numeric Expression»])"
Like 'C'-Language, can we able to store datas in that. If that so, can anyone give explanations on that.
thanks in advance.
Srinivasan
View 1 Replies
View Related
Jul 20, 2005
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
View 4 Replies
View Related