Help Insert XML Data
Jan 4, 2008
Dear All,
I'm new handle XML file, I have a question how to create procedure insert XML data into SQL table, let say we have table with schema like this
create table testXML
(col1 real,
col2 real,
col3 real)
I want to insert XML data
<firstdata>
<data>499,498.99,1.25</data>
<data>500.01,500,1.9</data>
<data>501.03,501.02,2.92</data>
</firstdata>
the result we need is
col1 col2 col3
499 498.99 1.25
500.01 500 1.9
501.03 501.02 2.92
thank's before
Regards
View 1 Replies
ADVERTISEMENT
Feb 26, 2015
I am writing a query to return some production data. Basically i need to insert either 1 or 2 rows into a Table variable based on a decision as to does the production part make 1 or 2 items ( The Raw data does not allow for this it comes from a look up in my database)
I can retrieve all the source data i need easily but when i come to insert it into the table variable i need to insert 1 record if its a single part or 2 records if its a twin part. I know could use a cursor but im sure there has to be an easier way !
Below is the code i have at the moment
declare @startdate as datetime
declare @enddate as datetime
declare @Line as Integer
DECLARE @count INT
set @startdate = '2015-01-01'
set @enddate = '2015-01-31'
[Code] .....
View 1 Replies
View Related
Apr 9, 2007
Hello
I have a problem with setting relations properly when inserting data using adonet. Already have searched for a solutions, still not finding a mistake...
Here's the sql management studio diagram :
and here goes the code1 DataSet ds = new DataSet();
2
3 SqlDataAdapter myCommand1 = new SqlDataAdapter("select * from SurveyTemplate", myConnection);
4 SqlCommandBuilder cb = new SqlCommandBuilder(myCommand1);
5 myCommand1.FillSchema(ds, SchemaType.Source);
6 DataTable pTable = ds.Tables["Table"];
7 pTable.TableName = "SurveyTemplate";
8 myCommand1.InsertCommand = cb.GetInsertCommand();
9 myCommand1.InsertCommand.Connection = myConnection;
10
11 SqlDataAdapter myCommand2 = new SqlDataAdapter("select * from Question", myConnection);
12 cb = new SqlCommandBuilder(myCommand2);
13 myCommand2.FillSchema(ds, SchemaType.Source);
14 pTable = ds.Tables["Table"];
15 pTable.TableName = "Question";
16 myCommand2.InsertCommand = cb.GetInsertCommand();
17 myCommand2.InsertCommand.Connection = myConnection;
18
19 SqlDataAdapter myCommand3 = new SqlDataAdapter("select * from Possible_Answer", myConnection);
20 cb = new SqlCommandBuilder(myCommand3);
21 myCommand3.FillSchema(ds, SchemaType.Source);
22 pTable = ds.Tables["Table"];
23 pTable.TableName = "Possible_Answer";
24 myCommand3.InsertCommand = cb.GetInsertCommand();
25 myCommand3.InsertCommand.Connection = myConnection;
26
27 ds.Relations.Add(new DataRelation("FK_Question_SurveyTemplate", ds.Tables["SurveyTemplate"].Columns["id"], ds.Tables["Question"].Columns["surveyTemplateID"]));
28 ds.Relations.Add(new DataRelation("FK_Possible_Answer_Question", ds.Tables["Question"].Columns["id"], ds.Tables["Possible_Answer"].Columns["questionID"]));
29
30 DataRow dr = ds.Tables["SurveyTemplate"].NewRow();
31 dr["name"] = o[0];
32 dr["description"] = o[1];
33 dr["active"] = 1;
34 ds.Tables["SurveyTemplate"].Rows.Add(dr);
35
36 DataRow dr1 = ds.Tables["Question"].NewRow();
37 dr1["questionIndex"] = 1;
38 dr1["questionContent"] = "q1";
39 dr1.SetParentRow(dr);
40 ds.Tables["Question"].Rows.Add(dr1);
41
42 DataRow dr2 = ds.Tables["Possible_Answer"].NewRow();
43 dr2["answerIndex"] = 1;
44 dr2["answerContent"] = "a11";
45 dr2.SetParentRow(dr1);
46 ds.Tables["Possible_Answer"].Rows.Add(dr2);
47
48 dr1 = ds.Tables["Question"].NewRow();
49 dr1["questionIndex"] = 2;
50 dr1["questionContent"] = "q2";
51 dr1.SetParentRow(dr);
52 ds.Tables["Question"].Rows.Add(dr1);
53
54 dr2 = ds.Tables["Possible_Answer"].NewRow();
55 dr2["answerIndex"] = 1;
56 dr2["answerContent"] = "a21";
57 dr2.SetParentRow(dr1);
58 ds.Tables["Possible_Answer"].Rows.Add(dr2);
59
60 dr2 = ds.Tables["Possible_Answer"].NewRow();
61 dr2["answerIndex"] = 2;
62 dr2["answerContent"] = "a22";
63 dr2.SetParentRow(dr1);
64 ds.Tables["Possible_Answer"].Rows.Add(dr2);
65
66 myCommand1.Update(ds,"SurveyTemplate");
67 myCommand2.Update(ds, "Question");
68 myCommand3.Update(ds, "Possible_Answer");
69 ds.AcceptChanges();
70
and that causes (at line 67):"The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_Question_SurveyTemplate". The conflict occurred in database
"ankietyzacja", table "dbo.SurveyTemplate", column
'id'.
The statement has been terminated.
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
at AnkietyzacjaWebService.Service1.createSurveyTemplate(Object[] o) in J:\PL\PAI\AnkietyzacjaWebService\AnkietyzacjaWebServicece\Service1.asmx.cs:line 397"
Could You please tell me what am I missing here ?
Thanks a lot.
View 5 Replies
View Related
Sep 11, 2006
Hello, I'm new to the forum and new to SQL, ASP.NET, etc. I am creating an intranet site for my company in VS 2005 and have run into a very annoying problem that I can't seem to solve. I have tried Googling it and came up empty. I have a database in SQL Express 2005 and my website will be accessing several tables within the database. I can retrieve info just fine and I can update, delete, etc just fine using gridview or other prebuilt tools, but when I add a few text boxes and wire a button to the SqlDataSource.Insert() command, I get a new record that is full of null values except for the identity key I have set. The kicker is that I am also using a master page and when I duplicate the web page without the master page link, everything works just fine. The following snippets show what I'm doing:<InsertParameters><asp:FormParameter Name="Name" Type="String" FormField="txtName" /><asp:FormParameter Name="Location" Type="String" FormField="ddlLocation" /><asp:FormParameter Name="Issue" Type="String" FormField="txtProblem" /></InsertParameters>Of course I match the formfields to the text boxes, create an onclick event for my button, the sqldatasource is configured correctly, it just doesn't work with the master page no matter what I do. Any help would be appreciated. Thanks
View 3 Replies
View Related
Apr 20, 2008
On my site users can register using ASP Membership Create user Wizard control.
I am also using the wizard control to design a simple question and answer form that logged in users have access to.
it has 2 questions including a text box for Q1 and dropdown list for Q2.
I have a table in my database called "Players" which has 3 Columns
UserId Primary Key of type Unique Identifyer
PlayerName Type String
PlayerGenre Type Sting
On completing the wizard and clicking the finish button, I want the data to be inserted into the SQl express Players table.
I am having problems getting this to work and keep getting exceptions.
Be very helpful if somebody could check the code and advise where the problem is??
<asp:Wizard ID="Wizard1" runat="server" BackColor="#F7F6F3"
BorderColor="#CCCCCC" BorderStyle="Solid" BorderWidth="1px"
DisplaySideBar="False" Font-Names="Verdana" Font-Size="0.8em" Height="354px"
onfinishbuttonclick="Wizard1_FinishButtonClick" Width="631px">
<SideBarTemplate>
<asp:DataList ID="SideBarList" runat="server">
<ItemTemplate>
<asp:LinkButton ID="SideBarButton" runat="server" BorderWidth="0px"
Font-Names="Verdana" ForeColor="White"></asp:LinkButton>
</ItemTemplate>
<SelectedItemStyle Font-Bold="True" />
</asp:DataList>
</SideBarTemplate>
<StepStyle BackColor="#669999" BorderWidth="0px" ForeColor="#5D7B9D" />
<NavigationStyle VerticalAlign="Top" />
<WizardSteps>
<asp:WizardStep runat="server">
<table class="style1">
<tr>
<td class="style4">
A<span class="style6">Player Name</span></td>
<td class="style3">
<asp:TextBox ID="PlayerName" runat="server"></asp:TextBox>
</td>
<td>
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server"
ControlToValidate="PlayerName" ErrorMessage="RequiredFieldValidator"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td class="style5">
<td class="style3">
<asp:DropDownList ID="PlayerGenre" runat="server" Width="128px">
<asp:ListItem Value="-1">Select Genre</asp:ListItem>
<asp:ListItem>Male</asp:ListItem>
<asp:ListItem>Female</asp:ListItem>
</asp:DropDownList>
</td>
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server"
ControlToValidate="PlayerGenre" ErrorMessage="RequiredFieldValidator"></asp:RequiredFieldValidator>
</td>
</tr>
</table>
Sql Data Source
<asp:SqlDataSource ID="InsertArtist1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" InsertCommand="INSERT INTO [Playerst] ([UserId], [PlayerName], [PlayerGenre]) VALUES (@UserId, @PlayerName, @PlayerGenre)"
ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>">
<InsertParameters>
<asp:Parameter Name="UserId" Type="Object" />
<asp:Parameter Name="PlayerName" Type="String" />
<asp:Parameter Name="PlayerGenre" Type="String" />
</InsertParameters>
</asp:SqlDataSource>
</asp:WizardStep>
Event Handler
To match the answers to the user I get the UserId and insert this into the database to.protected void Wizard1_FinishButtonClick(object sender, WizardNavigationEventArgs e)
{
SqlDataSource DataSource = (SqlDataSource)Wizard1.FindControl("InsertArtist1");
MembershipUser myUser = Membership.GetUser(this.User.Identity.Name);
Guid UserId = (Guid)myUser.ProviderUserKey;String Gender = ((DropDownList)Wizard1.FindControl("PlayerGenre")).SelectedValue;
DataSource.InsertParameters.Add("UserId", UserId.ToString());DataSource.InsertParameters.Add("PlayerGenre", Gender.ToString());
DataSource.Insert();
}
View 1 Replies
View Related
Oct 12, 2007
Hi,
i have a file which consists data as below,
3
123||
456||
789||
Iam reading file using bulk insert and iam inserting these phone numbers into table having one column as below.
BULK INSERT TABLE_NAME FROM 'FILE_PATH'
WITH (KEEPNULLS,FIRSTROW=2,ROWTERMINATOR = '||')
but i want to insert the data into table having two columns. if iam trying to insert the data into table having two columns its not inserting.
can anyone help me how to do this?
Thanks,
-Badri
View 5 Replies
View Related
Jan 29, 2008
I need to call a stored procedure to insert data into a table in SQL Server from SSIS data flow task.
I am currently trying to use OLe Db Destination, but I am not sure how to map inputs to OLE DB Destination to my stored procedure insert.
Thanks
View 6 Replies
View Related
Aug 11, 2015
Example of data in CSV are as follows:
"XXX","0001",-990039739 ,0 ,0 ,0 ,0 ,0 ,0
"ABC"," ",-3422054702 ,0 ,481385 ,0 ,0 ,0 ,0
"JJZ","0001",0 ,0 ,0 ,0 ,0 ,0 ,0Here's my format:
12.0
10
1 SQLCHAR 0 0 """ 0 "" ""
2 SQLCHAR 0 5 "","" 1 OKCCY SQL_Latin1_General_CP1_CI_AS
[Code] ....
View 5 Replies
View Related
Oct 8, 2007
I had a function like below :Public Sub Getdata2(ByVal query, ByVal db, ByVal name)
Dim selectSQL As StringDim con As SqlConnection
Dim cmd As SqlCommand
Dim reader As SqlDataReader
Trycon = New SqlConnection("User ID=xxx;password=xxx;persist security info=True;Initial Catalog=database1;Data Source=xxx.xxx.xxx.xxx.xxx,xxxxx;")
Dim username As String
username = Request.QueryString("username")
selectSQL = "SET DATEFORMAT DMY;Insert into table1(hse_num, st_name, proj_name, unit_num, postal, n_postal, flr_area, flr_sf, flr_rate, flr_ra_sf, land_area, land_sf, land_rate, lnd_ra_sf, prop_code, cont_date, title, sisv_ref, r_date, rec_num, source, username, DGP, Remarks, Sub_Code, caveat, consider, age) select hse_num, st_name, proj_name, unit_num, postal, n_postal, flr_area, flr_sf, flr_rate, flr_ra_sf, land_area, land_sf, land_rate, lnd_ra_sf, prop_code, cont_date, title, sisv_ref, r_date, rec_num, source, '" & username & "', DGP, Remarks, Sub_Code, caveat, consider, age from [yyy.yyy.yyy.yyy,yyyy].database2.dbo.table2 where " & querycmd = New SqlCommand(selectSQL, con)
con.Open()
reader = cmd.ExecuteReader()
lbl.Text = selectSQLCatch ex As Exception
lbl.Text = ex.Message
Finally
If (Not con Is Nothing) Then
con.Close()
con.Dispose()
End If
End Try
End Sub
'-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
May i know that how do i retrieve data from [yyy.yyy.yyy.yyy,yyyy].database2.dbo.table2 due to diffrent server, diffrent UID and Password
View 1 Replies
View Related
Feb 15, 2008
I am severly sleep deprived from this issue and this deliverable deadline is tomorrow, so anyone who can help me solve it will earn my deepest gratitude.
I have a form that inserts/updates to one table (table A), then inserts a record into another table (table B) (based on the user_id generated from the insert on the first table).
All of my queries/inserts work fine, UNTIL I get to my last insert (the one for table B). I don't get any compilation errors, and the insert/update to table A works just fine. But table B continues to be empty.
I have tried it by calling a stored procedure that does the update, as well as just writing the insert statement right into the code, and neither works.
If I execute the stored procedure directly on the db server and manually pass it the parameters, the insert works.At first I thought that the problem must be in the placement of my code on the page -- that perhaps that section wasn't even executing. But I know that it is because I can output my debugging that is contained right at the beginning of this section of code:
// Insert the print material requests into the INFO_REQUEST table
//Response.Write("CB1: " + print_materials);
//Response.Write("CB2: " + pricing);
//Response.Write("CB3: " + newsletter);SqlCommand myCommand2;
myCommand2 = new SqlCommand("info_request_insert", localConnection);
myCommand2.CommandType = CommandType.StoredProcedure;myCommand2.Parameters.Add(new SqlParameter("@user_id", user_id));
myCommand2.Parameters.Add(new SqlParameter("@current_customer", "N"));myCommand2.Parameters.Add(new SqlParameter("@print_product", "na"));
myCommand2.Parameters.Add(new SqlParameter("@print_materials", print_materials));myCommand2.Parameters.Add(new SqlParameter("@business_line", "na"));
myCommand2.Parameters.Add(new SqlParameter("@pricing", pricing));myCommand2.Parameters.Add(new SqlParameter("@demo", "na"));
myCommand2.Parameters.Add(new SqlParameter("@training", "na"));myCommand2.Parameters.Add(new SqlParameter("@ask_expert", "na"));
myCommand2.Parameters.Add(new SqlParameter("@recommend_friend_email", ""));myCommand2.Parameters.Add(new SqlParameter("@user_from", newsletter));
myCommand2.Parameters.Add(new SqlParameter("@cad_programs", "na"));myCommand2.Parameters.Add(new SqlParameter("@solver_programs", "na"));
myCommand2.Parameters.Add(new SqlParameter("@desktop_platform", "na"));myCommand2.Parameters.Add(new SqlParameter("@current_customer", "na"));
myCommand2.Parameters.Add(new SqlParameter("@submit_date", DateTime.Now));myCommand2.Parameters.Add(new SqlParameter("@comments", "na"));
try
{
myCommand2.Connection.Open();
myCommand2.ExecuteNonQuery();
myCommand2.Connection.Close();
}catch (Exception ex)
{Trace.Write("00-msg", ex.Message);if (myCommand2.Connection.State == ConnectionState.Open)
{
myCommand2.Connection.Close();
}
}
View 6 Replies
View Related
Apr 23, 2008
After reading through the forums ... Getting confused as I am new to the asp thing ...I want to type data into a text box, click a button (have this part down)I want the data from the text box to be inserted into a table in a sql db. Whats the best way to do this, tutorial or examples as well?
View 5 Replies
View Related
Mar 19, 2007
I would like to know how to import historical data with different tagname into same table with new tagname?....ie
Table Data:
old tagname = PS001_Pump2.RT_Daily
new tagname =PS001_Pump2.Daily_Runtime
DateTime TagName Value
2007-12-03 PS001_Pump2.Daily_Runtime 1.45
2007-12-04 PS001_Pump2.Daily_Runtime 2.07
2007-12-07 PS001_Pump2.RT_Daily 3.98
this is what i am looking to do:
DateTime TagName Value
2007-12-03 PS001_Pump2.Daily_Runtime 1.45
2007-12-04 PS001_Pump2.Daily_Runtime 2.07
2007-12-07 PS001_Pump2.Daily_Runtime 3.98
historical data is in same table as new tags, but i need to delete the old tags because of license constraint, but i need to move the old data into the new tags.
I am currently running sql2000.
View 2 Replies
View Related
Feb 27, 2007
What is wrong with this code the dropdowlist mainlyusing System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using System.Data.SqlClient;public partial class NewAccount : System.Web.UI.Page{ protected void Page_Load(object sender, EventArgs e) { } protected void NaccountButton_Click(object sender, EventArgs e) { if (Page.IsValid) { //Define data objects SqlConnection conn; SqlCommand comm; //read from web config string connectionString = ConfigurationManager.ConnectionStrings["OneBank"].ConnectionString; //Initialize connection conn = new SqlConnection(connectionString); //create command comm =new SqlCommand( "INSERT INTO Customer (FirstName, LastName, Street, City, State," + "Zip, Phone, Payee,AccountType)" + "VALUES(FirstName, LastName, Street, City," + "State, Zip, Phone, Payee,AccountType)", conn); //add parameters comm.Parameters.Add("@FirstName", System.Data.SqlDbType.NVarChar, 50); comm.Parameters["@FirstName"].Value=Firstname.Text; comm.Parameters.Add("@LastName", System.Data.SqlDbType.NVarChar, 50); comm.Parameters["@LastName"].Value=lastname.Text; comm.Parameters.Add("@Street", System.Data.SqlDbType.NVarChar, 50); comm.Parameters["@Street"].Value=street.Text; comm.Parameters.Add("@City", System.Data.SqlDbType.NVarChar, 50); comm.Parameters["@City"].Value=city.Text; comm.Parameters.Add("@State", System.Data.SqlDbType.NVarChar, 50); comm.Parameters["@State"].Value=state.Text; comm.Parameters.Add("@Phone", System.Data.SqlDbType.Int); comm.Parameters["@Phone"].Value=phone.Text; comm.Parameters.Add("@AccountType", System.Data.SqlDbType.NVarChar, 50); comm.Parameters["AccountType"].Value = dropdownlist1.SelectedValue.ToString(); //Enclose database in try catc finally try { //open connection conn.Open(); //execute the command comm.ExecuteNonQuery(); //reload query Response.Redirect("NewAccount.aspx"); } catch { //Display error Errormessage.Text= "Error submitting request!"; } finally { conn.Close(); } } }}
This is the table where thisis going
customer ------------- customerID pk generated automatically firtsname lastname street city statezipphoneaccountType
View 3 Replies
View Related
Mar 12, 2008
Hi people,
I've got a textbox and a button, I want to be able to put some text in the textbox and once i click on the button it will send it into the database. How would i go about doing this?
Please help
View 6 Replies
View Related
May 12, 2005
hi,
i have got a problem, iam creating an asp.net application, but i pretend to insert data in two different tables, but i have no ideia how to do this.Can you help to solve this problem? please..........
View 3 Replies
View Related
May 12, 2005
hi,
iam creating an asp.net application the database is created on the SQLSERVER, but i have a problem, i pretend to insert data in to 2 tables and i have no ideia how to do this. Can you help me to solve this problem? Please.....
View 1 Replies
View Related
Jul 21, 2005
I have a table changereport and network info.i insert a record with date of insertion plus chagereport id.then I have a xml file
<ChangeReport> <Network-Info> <Version> 2.0</Version> <Highest-Ver> 2.0</Highest-Ver> <Description> WinSock 2.0</Description> <System-Status> Running</System-Status> <Max> 2.0</Max> <IP-address> 192.168.142.1</IP-address> <Domain-Name> samin</Domain-Name> <UDP-Max> 2.0</UDP-Max> <Computer-Name> SAMIN</Computer-Name> <User-Name> samin</User-Name> </Network-Info></ChangeReport>
I want's to insert a record in network info table where field name are nodes name data should be the one against those nodes in xml file .How can I do it I am using sql server
View 2 Replies
View Related
Sep 8, 2005
How to upload file data into ms sql server?
thanks
View 3 Replies
View Related
Dec 3, 2005
I'm trying to insert data into my local SQLExpress database by using on buttonclick from a asp.net page. I run the page, hit submit and I check my database and there's no data entered. Does the following code look correct or do I need to look at other things...
Thanks in advance!
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
If Not Page.IsPostBack Then
Dim mycommand As SqlCommand
Dim myconnection As New SqlConnection
Dim insertCmd As String
Dim guidResult As String = System.Guid.NewGuid().ToString()
Dim isAffected As Integer
myconnection = New SqlConnection()
myconnection.ConnectionString = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True"
myconnection.Open()
insertCmd = "Insert Into [tbl_Messaging] ([UserName], [Admired], [message], [messageID], [datesent]) Values (@Username, @Admired, @message, @messageID, @dateSent)"
mycommand = New SqlCommand()
mycommand.CommandType = CommandType.Text
mycommand.CommandText = insertCmd
mycommand.Parameters.Add("@username", SqlDbType.VarChar).Value = Profile.Username
mycommand.Parameters.Add("@admired", SqlDbType.VarChar).Value = TextBox1.Text
mycommand.Parameters.Add("@message", SqlDbType.VarChar).Value = TextBox2.Text
mycommand.Parameters.Add("@messageid", SqlDbType.UniqueIdentifier).Value = guidResult
mycommand.Parameters.Add("@datesent", SqlDbType.SmallDateTime).Value = DateTime.Today.ToShortDateString
isAffected = mycommand.ExecuteNonQuery()
myconnection.Close()
End If
End Sub
View 1 Replies
View Related
Feb 21, 2006
What have I done wrong with this script?I'm getting an error The ConnectionString property has not been initialized.
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.InvalidOperationException: The ConnectionString property has not been initialized.
Source Error:
Line 73: myCommand.Parameters.Add(New SqlParameter("@Activationcode", SqlDbType.nVarChar, 50))Line 74: myCommand.Parameters("@Activationcode").value = activecode.valueLine 75: myCommand.Connection.open()Line 76: tryLine 77: myCommand.ExecuteNonQuery()
Dim loConn as New SqlConnection(ConfigurationSettings.AppSettings("MM_CONNECTION_STRING_HWB"))Dim myCommand as SqlCommandDim InsertCmd as StringinsertCmd = "insert into Login values (@Username, @Password, @email, @Activationcode);"myCommand = New SqlCommand(InsertCmd, loConn)myCommand.Parameters.Add(New SqlParameter("@Username", SqlDbType.nVarChar, 50))myCommand.Parameters("@Username").value = Username.valuemyCommand.Parameters.Add(New SqlParameter("@Password", SqlDbType.nVarChar, 50))myCommand.Parameters("@Password").value = Password.valuemyCommand.Parameters.Add(New SqlParameter("@email", SqlDbType.nVarChar, 50))myCommand.Parameters("@email").value = email.valuemyCommand.Parameters.Add(New SqlParameter("@Activationcode", SqlDbType.nVarChar, 50))myCommand.Parameters("@Activationcode").value = activecode.valuemyCommand.Connection.open()myCommand.ExecuteNonQuery()myCommand.Connection.Close()
View 1 Replies
View Related
Jun 6, 2006
Hi I was watching this beginner webcast
Lesson 8: Obtaining Data from a SQL Server 2005 Express Edition Database
http://msdn.microsoft.com/vstudio/express/visualcsharp/learning/#beginners
I tried to do the same exact thing.
But when I tried to insert data in the newly created table I got the follwoing error window:
Invalid value for cell (row1, column 2)Then changed value in this cell was not recognized as valid..Net Framework Data Type: Byte[]Error Message; You cannot use the Result pane to set this Field data to values other than NULL.
Type a value appropriate for the data type or press ESC to cancel change.
Any Idea why thing happing.
View 3 Replies
View Related
Jun 13, 2006
Need some help. In my db I created 3 tables. 1- tblClient 2- tblCategories 3- tblClient_Cat ( colums: 1) client_id 2) cat_id).What I'm trying to do is create a sp to insert data from a web form into the tblClient and into the tblCat using the same sp. this is all I got so far.CREATE PROCEDURE [usp_insetClient](@ClientName varchar(25),@Contact varchar(100),@Tel char(13),@Fax char(13),@WebAdd varchar (250),@email varchar(250),@CatID int) ASBegininsert tblClient(Client_name,client_contact,Lient_tel,client_fax,client_webadd,client_email)values(@ClientName,@Contact,@Tel,@Fax,@WebAdd,@email)select @@identityinsert tblclient_cat(cat_id,client_id)values(@Catid,@@identity)(I tried to use this sp but didnot work)as you can see I need help inserting the client_id just created by the db into the tblclient_cat since the cat_id is passed from the web formThanksErnesto
View 1 Replies
View Related
Apr 27, 2000
Hi,
If I have 2 tables (Source and Destination) which have the same fields except the fields are not in same order, what is a practical way to insert all data from Source into Destination? I know that I can do insert with select but then I need to specify every single fields since the fields positions are different between Source and Destination.
Thank you for any help.
View 2 Replies
View Related
Dec 11, 2005
Hello I am developing a VB application and my backend is MSSQL 2000.
I have 2 Database Say DB1 and DB2.Both are in Same Server.
Now i need to get data from DB1 and insert into DB2.
Eg.
Insert into tablefromDB1 Select * from ServerName.dbo.TableFromDB2
how can i do this with username
insert into tablefromDB1 select * from Servername.username.TablefromDB2
When i put like this it pops up a error.
Need help
with regards
View 2 Replies
View Related
Dec 22, 2006
Hello,
I'm trying to make a query to insert data form xls file to my database and it's working
Here's my code
INSERT INTO MY_TABLE
SELECT SAP_NBR, PERIOD, ANSWER, OSAT, SCORE
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="C:
ipTMP_Aubrygsi_osat.xls";
User ID=Admin;Password=;Extended properties=Excel 8.0')...Sheet1$
WHERE SAP_NBR is not null
AND PERIOD is not null
AND ANSWER is not null
AND OSAT is not null
AND SCORE is not null
And I would like to verify if there no sap_nrb and period already the same in the database
so I add :
AND SAP_NBR not in MY_TABLE.SAP_NBR
AND PERIOD not in MY_TABLE.PERIOD
but it doesn't work
what's the correct syntax to make it work ???
Thanks a lot & merry Xmas... :)
View 8 Replies
View Related
Mar 10, 2006
SALAM SIR,
CREATE TABLE Airlines_Master
( Aircode CHAR(2),
Airlines_name VARCHAR(15))
THIS IS MADE A TABLE IN SQLSERVER 2000.BUT HOW CAN INSERT DATA INTO THIS TABLE?
View 3 Replies
View Related
Apr 10, 2006
hi
It seems that my code can insert data into memory, but not into the database. What I mean is that after "insert data", I can "read data",
which I just insert. When I check the actual database table, it didn't
get updated.
I am using VS 2005 and table designer. Regarding to this problem, is
it related to any setting of setup of the database? I check the code,
and I have no idea how it occurs.
private static string connectionString = null;
private static SqlConnection connection = null;
private static string commandString = null;
private static SqlCommand command = null;
private static SqlDataReader reader = null;
static void Main(string[] args)
{
connectionString = ConfigurationManager
.ConnectionStrings["appDatabase.Properties.Settings.databaseConnection String"]
.ConnectionString;
connection = new SqlConnection(connectionString);
try
{
// Insert data
commandString = @"INSERT INTO userTable
(userID, permissionLevel, mobile, emailAddress, mailAddress, pager) VALUES(1, 2, '123', 'aa@a.com', 'oz', 'no')";
SqlCommand command = new SqlCommand(commandString, connection);
connection.Open();
command.ExecuteNonQuery();
connection.Close();
// Read data
commandString = @"select * from userTable";
command = new SqlCommand(commandString, connection);
connection.Open();
reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(reader["permissionLevel"].ToString());
}
connection.Close();
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
regards
figo2476
View 5 Replies
View Related
May 2, 2008
I'm passing data into sql proc. then insert into two diffrent tables. The two table dont have 1:1 relations. I'm trying to come up with the best way to insert the values into these two table. Here is what I have-- any suggestion?
fyi. I will love the proc to check errors during the process
CRETAE PROCEDURE dbo.two_tableProc
@File1 varchar(5),
@File2 varchar(5),
@SystemDate datetime,
@RecordCt1 varchar(100)
@RecordCt2 varchar(100)
@RecordID INT
as
INSERT INTO tbl1
(
File1,
RecordCt1,
SystemDate
)
VALUES (@File1,@RecordCt1,@SystemDate)
INSERT INTO tbl2
(
File2,
RecordCt2,
RecordID
)
VALUES (@File2,@RecordCt2,@RecordID)
GO
Josephine
View 1 Replies
View Related
Dec 15, 2014
I tired to insert data from xml to a table .but only null value is getting inserted into the table.The query is attached below.
create table Detail_test1
(
DetailIdInt,
LaIdInt,
OcCodeVarchar,
BIdINT,
RateINT)
Declare @xml XML
set @xml='<ArrayOfBDetailLine>
[code]....
View 1 Replies
View Related
Jun 14, 2007
In oracle, I use the following script in trigger:
INSERT INTO TABLE1 (CITY, STATE) VALUES (:new.city,:new.state);
I am wondering how to insert new data in SQL server. Thanks!
View 5 Replies
View Related
Jan 4, 2008
Hi my first post so be gentle :D
I have a table which has a column of type binary(200)
I want to put some test data into this table just using sql server managment studio express
how do i do this i keep getting message which says
"the changed value in this cell was not recognized as valid .NET framework data type: Byte[]"
Thanks,
Adam
View 1 Replies
View Related
Jul 20, 2005
hi thereCreated sproc - it stops dead in the first lineWhy ????Thanks in advanceCREATE PROCEDURE [dbo].[test] ASinsert into timesheet.dbo.table1 (RE_Code, PR_Code, AC_Code, WE_Date,SAT, SUN, MON, TUE, WED, THU, FRI, NOTES, GENERAL, PO_Number,WWL_Number, CN_Number)SELECT RE_Code, PR_Code, AC_Code, WE_Date, SAT, SUN, MON, TUE,WED, THU, FRI, NOTES, GENERAL, PO_Number, WWL_Number, CN_NumberFROM dbo.WWL_TimeSheetsWHERE (RE_Code = 'akram.i') AND (WE_Date = CONVERT(DATETIME,'1999-12-03 00:00:00', 102))GO
View 6 Replies
View Related
Jun 8, 2006
I'm using Microsoft SQL Server Management Studio to access my database.
When I open a table and trying to insert data into the data, I received
this error:
No row was updated.
The data in row 8 was not committed.
Error Source: .Net SqlClient Data Provider.
Error Message: The conversion of a char data type to a datetime type resulted in an out-of-range value.
The statement has been terminated.
Correct the errors and retry or press ESC to cancel the change(s).
Again, I'm not trying to change the datatype. I only want to add data into the table. The column is varchar datatype.
View 3 Replies
View Related
