Hi, I was wondering if anyone knows how to import an sql script to the MSSQL server using the SQL Server enterprise manager? I have some scripts that I want to use and I don't want to manually create the database if I have the scripts.
View 1 RepliesI am trying to insert data into two different tables. I will insert into Table 2 based on an id I get from the Select Statement from Table1.
Insert Table1(Title,Description,Link,Whatever)Values(@title,@description,@link,@Whatever)Select WhateverID from Table1 Where Description = @DescriptionInsert into Table2(CategoryID,WhateverID)Values(@CategoryID,@WhateverID)
This statement is not working. What should I do? Should I use a stored procedure?? I am writing in C#. Can someone please help!!
Hello friends....
I am looking for 2 things(using c#.net or vb.net and sql svr 2000)
1.convert data from sql server 2000 database (say customers table from northwinds database) to a text file(separated by commas or just plain space)
2.Insert the data from text file back to database.
Can someone pls give me the detailed code to achieve this....really need this on urgent basis.......Thank You.
How can I insert a value that includes ' ?
My insert string looks like this: "INSERT INTO table(field1) VALUES ( ' " + value1 + " ' )"
When value1 has ' in it, I get an error.
I have the following code for SQL2000:
It only works if the sub-query returns only 1 values.... anyone knows how I can solve this?
thx a lot!!!
INSERT INTO Table1(SourceName, CreatedBy, CreateDate, IsSuccess, datadate)
SELECT 'Data_01', user, GetDate(), 1,
(
SELECT DISTINCT T1_Dummy.[TRANSACTION DATE]
FROM T1
WHERE T1.[TRANSACTION ID] NOT IN
(
SELECT T.TRANSACTION_ID
FROM T
)
)
FROM T1
GROUP BY [Transaction Date]
Hi,
I am moving one database to another (different scemas) using SSIS. The destination database uses autogenerated keys. In order to preserve referential integrity I have to keep track of the keys generated when doing inserts in various tables. I use a staging table to save the keys generated by the insert command. I use this as a lookup table later on to find the foreign keys in order to link newly inserted records. The only way that I could find to get the key back after an insert was to create a Script component that does the insert and then executes "SELECT @@IDENTITY". After that I use an OLE DB Command to update the record in the staging database. Is there a better way of doing this? It seems to me that this is not a proper use of the Script component, but if I want to use OLE DB destination or OLE DB Command I cannot get the key back. Also, I am not allowed to use any stored procedures or change the destination database in any way.
Hello.
I need to insert some records to an accounting table and calculate the balance after that. Thus, other users can be trying to do the same. How to lock the db and make the other users wait until the right moment? I'm using SqlDataSource to do that.
Thanks.
I have populated an SQLdata table from an XML datasource usinmg the bulk command. In my SQL table is a new column that is not in the XML table which I would like to set to a default value. Would anyone know the best way to do this. So far I can's see how to add this value in the Bulk command. I am happy to create a new command that updates all the null values of this field to a default value but can't seem to do this either as a SQLdatasource or a APP Code/ Dataset. Any suggestions or examples where I can do this.Many thanks in advance
View 1 Replies View RelatedHi,I m using Microsoft Visual Studio 2005 and SQL server 2000. I have 2 textboxes and a button, what i wanna do is, when i hit the button, the values in textboxes should be inserted into DB. Would you please help me? Thanks in advance.
View 1 Replies View RelatedHow to insert 100 record at a time by explicit inserting of identity column i.e.., by setting identity column to false
View 4 Replies View RelatedHi friends,I have one text box on my form.i need to insert the data from tat text box to DB without clicking on any button.Now i have written the code under text changed event of that text box.So it is inserting whenever i click on that form.Besides this i need to insert data when i close tat window.But it is not inserting when i close tat window.Plse help me.Thanks in advance
With RegardsLijo Rajan
I have created a form with several fields etc and validation.
After pressing the submit button i have a
if Page.IsValid then .....etc
But in this then bit I want to do a
INSERT the form details to the db.
I have done inserts etc via gridView etc but I just need a form that lets someone enter info and submit etc, so do not now where to or how to place this code to connect then insert etc
thanks
Why on earth is this not working, It WILL insert if there are NO apostheres (" ' "), but if I say like, "Cedric's Group" It will error out. I thought this approach was suppose to handle this character?
Thanks for any insight, String sqlStr = "Insert into cardgroups (username,groupname,insertdt,groupid) values (@username,@groupname,@insertdt,@groupid)";
SqlCommand cmd = new SqlCommand(sqlStr, connect);
cmd.Parameters.AddWithValue("@username", Session["memberusername"].ToString());
cmd.Parameters.AddWithValue("@groupname",this.namebox.Text.ToString());
cmd.Parameters.AddWithValue("@insertdt", DateTime.Now.ToString());
cmd.Parameters.AddWithValue("@groupid", myGroupStr);
connect.Open();
cmd.ExecuteNonQuery();
connect.Close();
I fixed all the free text stuff but now I have another problem. I created a web app one page to enter data and anoter to display it. I have a multiple row text box and if you enter something with returns it it when you display the data it ignores the returns and puts everything on the same line BTW the column type is text
example
this
"want to hest this out.
========================
now I am going to put in some test data and see how it comes out
finished"
will come out like this
"want to hest this out.========================now I am going to put in some test data and see how it comes out finished"
how do you fix this?
I want to insert a recorde into my table (2 column) : name and Id
id is a primary key and this is the error MSG im getting everytime im trying to insert a record
Error:Cannot insert explicit value for identity column in table 'Customer' when IDENTITY_INSERT is set to OFF.
what is the problem
here is the code :protected void Button2_Click(object sender, EventArgs e)
{
try
{string str = "data source=.;integrated security=true; initial catalog=NH_Project2";
SqlConnection conn = new SqlConnection(str);SqlCommand comm = new SqlCommand("insert into [Customer](Name,Id) values(@custName,@custId)", conn);
comm.CommandType = CommandType.Text;comm.Parameters.AddWithValue("@custName", TextBox1.Text);comm.Parameters.AddWithValue("@custId", TextBox2.Text);
conn.Open();
int rowaff = (int)comm.ExecuteNonQuery();if (rowaff == 1)
{Label4.Visible = true;Label4.Text = "one customer has added to our data base";
}
else
{Label4.Visible = false;Label4.Text = "not added try again";
}
}catch (Exception ex)
{ Response.Write("Error:" + ex.Message);
}
}
Hey all,
Quick question for ya. If I wanted to insert 1 value into 1 table and another value into another table, how would I do that with closing connections and reopening and whatnot? I am using Visual Web Dev and SQL server 2005 express. I have been trying to mess around with it but I can't figure it out. Here is the situation.
I want to insert a GroupName and GroupDescription that a user fills in in 2 text boxes named GroupNametxt and GroupDescriptiontxt. This data will go into the "Group" table.
I then also want to Insert the data UserID (taken from the aspnet ID table), and GroupName from the Grouptxt.text into a GroupMembership table.
How would I do all this in 1 button click? I know some basic TSQL but I don't know how to handle the opening and closing of connections and whatnot.
Thanks,
Chris
When using a SQL Server DateTime column to store just times, e.g. '6:00 PM' I've noticed some weird inconsistency.
If I insert a time in a SQL statement, such as... INSERT INTO myTable (StartTime) VALUES ('6:00 PM')
It winds up going in as '1/1/1900 6:00 PM'. This is okay. This is in fact what I want (unless of course the base day for sql server is in fact 12/30/1899, which is my next question).
If, on the other hand, I insert a time value by hand, using SQL Server Management Studio, then after I insert '6:00 PM' it winds up as '<Today's Date> 6:00 PM', i.e. '1/20/2008 6:00 PM'.
Also, if I insert it using a SQL Parameter, such as...
da.InsertCommand.Parameters.Add("@StartTime", SqlDbType.DateTime, 8, "StartTime")
...Then, if StartTime is 6:00 PM again, it also winds up going in as '<Today's Date> 6:00 PM'.
Is there a way to control this behavior (other than to programmatically append a base date to the time I'm inserting)?
Finally, what is the base day for SQL Server 2005? Is it 1/1/1900 or 12/30/1899? I think I've seen both.
Thanks!
Aaron
Hi folks,
I'm in trouble getting an dropdownlist inserting its selected value into an .mdb
I'll insert my datasource code. There is another dropdownlist in this formview working good.
There is a datafield ID_Org_Einheit in the database to gather the value.
Could you please look this over?
Many thanks
Rosi
This is the dropdownlist
<asp:DropDownList ID="DDL_Org_alle" runat="server" DataSourceID="SDS_Org_alle" DataTextField="OE_gegliedert"
DataValueField="ID_org">
</asp:DropDownList>
and the datasource
<asp:SqlDataSource ID="SDS_insert_FB_log" runat="server" ConnectionString="<%$ ConnectionStrings:FahrtenbuchConnectionString %>"
InsertCommand="INSERT INTO tab_Fahrtenbuch_log([Datum_Eintragung], [Fahrer], [ID_Org_Einheit], [pol_Kennzeichen], [ID_Einsatzzweck], [Strecke_von], [Strecke_bis], [Zeit_von], [Zeit_bis], [km_von], [km_bis]) VALUES (@Datum_Eintragung,@Fahrer,@ID_Org_Einheit,@pol_Kennzeichen,@ID_Einsatzzweck,@Strecke_von,@Strecke_bis,@Zeit_von,@Zeit_bis,@km_von,@km_bis)" ProviderName="<%$ ConnectionStrings:FahrtenbuchConnectionString.ProviderName %>"
SelectCommand="SELECT Datum_Eintragung, Fahrer, ID_Einsatzzweck, Strecke_von, Strecke_bis, pol_Kennzeichen, ID_Org_Einheit, Zeit_von, Zeit_bis, Bemerkung, km_von, km_bis FROM tab_Fahrtenbuch_log">
<InsertParameters>
<asp:FormParameter FormField="Datum_EintragungTextBox" Name="Datum_Eintragung"/>
<asp:FormParameter FormField="FahrerEintragTextBox" Name="Fahrer"/>
<asp:FormParameter FormField="DDL_Org_alle" DefaultValue="1" Name="ID_Org_Einheit"/>
<asp:FormParameter FormField="polKennzTextBox" Name="pol_Kennzeichen"/>
<asp:FormParameter FormField="DDL_Einsatzzweck" DefaultValue="1" Name="ID_Einsatzzweck" Direction="Input" Size="3" /><asp:FormParameter FormField="StartTextBox" Name="Strecke_von"/> <asp:FormParameter FormField="ZielTextBox" Name="Strecke_bis"/>
<asp:FormParameter FormField="AbfahrtTextBox" Name="Zeit_von"/>
<asp:FormParameter FormField="AnkunftTextBox" Name="Zeit_bis"/><asp:FormParameter FormField="kmFahrtBeginnTextBox" Name="km_von"/> <asp:FormParameter FormField="kmFahrtEndeTextBox" Name="km_bis"/>
</InsertParameters></asp:SqlDataSource>
VWD 2005 Express. Visual Basic. Sql Server 2005.
How may I insert a record into a table from code behind (I am doing this without any entries to a form). The connection name is GoodNews_Extranet. The table is Login. The fields I want to write are UserId and dtTime. Thanks for any help. The insert command is:
INSERT INTO [Login] ([SystemUserId], [dtTime]) VALUES (@SystemUserId, @dtTime)
I will provide the @ parameters programmatically. I just need to know the commands I should use in Visual Basic to actually execute the INSERT command. Thanks for the help.
If anyone can see what else I need to insert the stuff below into my database, I'd really appreciate it. I get intellisense, but also this error: Cannot insert the value NULL into column 'UserID' strUserName = cuwCreate.UserName.ToString
strUserID = Membership.GetUser(strUserName).ProviderUserKey.ToString()
Session("NewUserName") = strUserName
Session("NewUserID") = strUserID <asp:SqlDataSource ID="srcCreateUser" runat="server" ConnectionString="<%$ ConnectionStrings:webConn %>"
InsertCommand="sp_CreateUser" InsertCommandType="StoredProcedure" SelectCommand="sp_CreateUser"
SelectCommandType="StoredProcedure" UpdateCommand="sp_CreateUser" UpdateCommandType="StoredProcedure">
<SelectParameters>
<asp:SessionParameter Name="UserID" SessionField="NewUserID" Type="String" />
<asp:ControlParameter ControlID="UserName" DefaultValue="" Name="UserName" PropertyName="Text"
Type="String" />
<asp:ControlParameter ControlID="Email" Name="Email" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="txtFirstName" Name="FirstName" PropertyName="Text"
Type="String" />
<asp:ControlParameter ControlID="txtLastName" Name="LastName" PropertyName="Text"
Type="String" />
<asp:ControlParameter ControlID="txtTeacher" Name="Teacher" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="txtGradYr" Name="GradYr" PropertyName="Text" Type="Int32" />
</SelectParameters>
<InsertParameters>
<asp:SessionParameter Name="UserID" SessionField="NewUserID" Type="String" />
<asp:Parameter Name="UserName" Type="String" />
<asp:Parameter Name="Email" Type="String" />
<asp:Parameter Name="FirstName" Type="String" />
<asp:Parameter Name="LastName" Type="String" />
<asp:Parameter Name="Teacher" Type="String" />
<asp:Parameter Name="GradYr" Type="Int32" />
</InsertParameters>
</asp:SqlDataSource>Dim obj As SqlDataSource = cuwRegister.ContentTemplateContainer.FindControl("srcCreateUser")
obj.Insert()
Does anyone have a good example of how to insert data using System.Data.Linq? All the examples I've seen do something likeNorthwindDataContext db = new NorthwindDataContext();var x = new Product() {...}; db.Products.Add(x);db.SubmitChanges(); However I'm not seeing an Add method on System.Data.Linq.Table<T>. Has this changed? Could I somehow not be generating my model correctly?
View 2 Replies View RelatedHi, is there an equivalent to the mysql's " INSERT INTO table () VALUES ();"? The fields don't allow null values, and there is a primary key with auto increment. Any ideas?Thanks a lot.
View 7 Replies View RelatedHi i have a insert stored procedure, which i need to insert a label.text which is 12.99
the problem i have is that it is inserting as 13.00 any ideas what i need to do??Dim Price As Decimal = CostLabel.Text then i am inserting with this param....
command113.Parameters.Add("@Cost", SqlDbType.Decimal).Value = Price
Using visual web Developer 2008 express edition. I am trying to insert 2 text boxes with a label
of FirstName and LastName into a SQL express database. I am using a submit button to accomplish this.
I have a good connection to the database called FormData. When the textbox is filled in with firstname and lastname
and the submit button is pressed, I want to be able to go to the database and do a select * from tablename and see
the updated table....
Here is my code so far: any help would be greatly appreciated....<%@Import Namespace="System.Data.SqlClient" %>
<%@Import Namespace="System.Data" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Untitled Page</title>
<style type="text/css">
#form1
{height: 292px;width: 431px;
}
</style></head>
<body>
<form id="form1" runat="server"><asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:FormDataConnectionString %>"
SelectCommand="SELECT * FROM [Table_1]"></asp:SqlDataSource>
<p style="width: 78px; height: 19px; font-size: small; font-family: Verdana; margin-right: 0px; top: 76px; left: 13px; position: absolute;">
FirstName</p>
<p style="width: 80px; height: 21px; font-family: Verdana; font-size: small; top: 138px; left: 10px; position: absolute;">
LastName</p>
<asp:TextBox ID="TextBox1" runat="server"
style="top: 70px; left: 120px; position: absolute; height: 25px; width: 193px"></asp:TextBox><asp:TextBox ID="TextBox2" runat="server"
style="top: 136px; left: 118px; position: absolute; height: 26px; width: 195px"></asp:TextBox><asp:Button ID="Button1" runat="server"
style="top: 204px; left: 28px; position: absolute; height: 29px; width: 68px"
Text="Submit" /><asp:Button ID="Button2" runat="server"
style="top: 204px; left: 126px; position: absolute; height: 29px; width: 75px"
Text="Reset" />
</form>
</body>
</html>
Hello,
I have got a database with a datetime field. I insert a date into the database from a textbox. All is fine if someone enters a date. If someone enters nothing I want a null to be inserted. How do I do this?
Grant
Im trying to insert a decimal into SQL 2000 using a stored procedure. I can successfully insert it using a direct T-SQL statement inside the code, but I cant insert it correctly when calling for a stored procedure.
For instance 12.5 will insert as 12.
Here is my SQL Parameter code:
[code]
MyCommand.Parameters.Add(New SqlParameter("@Num", SqlDbType.Decimal))
MyCommand.Parameters("@Num").Precision = 5
MyCommand.Parameters("@Num").Scale = 2
MyCommand.Parameters("@Num").Value = Convert.ToDecimal(TextBox1.Text)
[/code]
I also declared @Num to be a decimal in my Stored Procedure.
As you can see I give the Parameter more than enough detail about the data type as well as convert the textbox value to a decimal, but it still rounds the value to an integer like data.
Again using a direct T-SQL statement inside the code works OK, but the stored procedure will not.
ANyone got any ideas why this is happening.
I've got a string value in C# that contains:
"<?xml version="1.0"?><?qbxml version="2.0"?><QBXML><QBXMLMsgsRq onError="stopOnError"><JournalEntryAddRq requestID="1"><JournalEntryAdd><TxnDate>2003-11-18</TxnDate><JournalDebitLine><AccountRef>
<FullName>1200</FullName>
</AccountRef>
<Amount>160.06</Amount>
<Memo>613663</Memo>
<ClassRef>
<FullName>Buffalo</FullName>
</ClassRef></JournalDebitLine><JournalCreditLine><AccountRef>
<FullName>5687</FullName>
</AccountRef>
<Amount>160.06</Amount>
<Memo>613663</Memo>
<ClassRef>
<FullName>Buffalo</FullName>
</ClassRef></JournalCreditLine><JournalDebitLine><AccountRef>
<FullName>1200</FullName>
</AccountRef>
<Amount>133.85</Amount>
<Memo>300931</Memo>
<ClassRef>
<FullName>Buffalo</FullName>
</ClassRef></JournalDebitLine><JournalCreditLine><AccountRef>
<FullName>5687</FullName>
</AccountRef>
<Amount>133.85</Amount>
<Memo>300931</Memo>
<ClassRef>
<FullName>Buffalo</FullName>
</ClassRef></JournalCreditLine></Journ
alEntryAdd></JournalEntryAddRq></QBXMLMsgsRq></QBXML>"
I'm passing that string to an insert stored procedure that hits a varchar field (varchar 5000).
problem is, nothing is showing up in the database.
there are no errors, but the field I have shows nothign in it after the insert.
if i run a test and set my string to "this is a test" it goes into the table from the SP_insert no problem.
So i'm figuring, somehow the formatting of the XML string in C# isn't kosher with MS SQL.
the above text is the exact string value from VS.NET command output (? mystring).
I'm really in need of help here guys. I'm trying to log this XML result into a log table for reference if there are ever any problems I can look in the log table @ the xml.
but i can't get it to insert.
Help!
Dear All,
In sql-server2000
In a table i am having a column of datatype varchar(8000).
While inserting the record through executenonquery, i am insert only
255 characters rest of the characters are getting trucated.
My question in how i will able to insert the row of that particular
column more than 255 characters
Thanx in advance.
Regards
Hey Guys:
I got a quick question, I am trying to insert five text boxes information into a sql database. I can easily do this by leaving the textboxes blank, and having a user enter a value then simply inserting the value into the database.
My issues is that I am creating a review page. The first page users will enter in values into a text box, then the second page(where the insert statement is located) displays the results, along with another query to find the user_id from the system.user. I keep getting an error stating that the viewstate is unknown, or something along those lines.
Basicly I think my error comes because I am passing the variables across the pages, then calling them, and then inserting them into the textbox values. I'm just wondering if you guys could tell me how to insert values from the past page, a tutorial would be great, or a site!
Oh: SqlServer, Asp.net, VB.net
Thanks,
Brian Sierakowski
Greetings, SQLanoids,
When I insert a row of data into a table via a stored procedure, the row SOMETIMES gets inserted at the bottom, and sometimes gets inserted at another random location (top, midding, etc). I am using Microsoft SQL Server.
Dea anybody know the cause of this? Or how to ensure that the row gets inserted at the bottom?
I am a novice SQL Server user, but this seems like a simple problem that the more advanced users solved long ago. Thanks in advance!
I want to insert a new record into my db in asp.net. Im programming in vb and using an sql server database. i know this is a begginers question but that's exactly what i am. Thanks in advance
View 1 Replies View RelatedPublic Function insertReport(ByVal userID As String, ByVal taskID As Integer) As DataSet
Dim ds As New DataSet
Dim da As New SqlDataAdapter("INSERT INTO report(userID, taskID) VALUES ('" + userID + "', " + taskID + ")", cn)
cn.Open()
da.Fill(ds)
cn.Close()
Return ds
End Function
Above is my code I used to invoke when inserting a record. But i receive an error message when i try to insert the integer. I cannot see where the problem lies..my datatype in sql server is int. Can anyone see what is wrong with it. Thank you in advance.
Hi all,
I'm having a problem saving the information from my web form into my sql database when I clicked on the 'Submit' button.
This is the error message
Server Error in '/Helpdesk' Application.
ExecuteNonQuery: Connection property has not been initialized.
I've attached my code below. Please advise me on what is wrong... How should I initialise the ExecuteNonQuery.
========= start code ====================
Dim MySQL As String
MySQL = ""
Dim MyConn As SqlConnection = New SqlConnection()
Dim MyCmd As SqlCommand = New SqlCommand()
MyConn.ConnectionString = "Server=ESAWEB2;Database=Helpdesk;Trusted_Connection=True;"
MySQL = "INSERT INTO TBL_TROUBLE_TICKET (Priority) values (' ddlpriority ')"
MyConn.Open()
MyCmd.ExecuteNonQuery()
MsgBox("Record Inserted")
=======end code ===============