Displaying Error When Using Insert Command
Jan 27, 2006
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:intranetnewConnectionString %>"
InsertCommand="INSERT INTO timeoffcalc(Typeoftime, amountoftime, employeeID) VALUES (@TypeofTime, @Amountoftime, @emplID)"
I have the emplyID and Typeof time as a PK. When the user enters a duplicate value it just gives them the error page. Can i set a label or something to notify the user of the error instead of the error page?
View 5 Replies
ADVERTISEMENT
Jun 4, 2007
"INSERT INTO tblEquip(buydate,Country,feature,pc,Provider,Serial,Status,Warranty,Year,Typeid ) VALUES ('12/12/2008','Viet Nam','Supper Power ',0,'IBM','ABCDEF','Out of warranty','12/12/2008',1965,5);"
I use Visual 2005 IDE and code in C# , with an MS Access 2003 Databse.
Above SQL command is gotten from Debug.
It works well when i paste into a query in MS Access 2003.
But in my project, it return an error : Syntax error in INSERT INTO statement when I use a try catch statement.
Where is my wrong ? Please help me.Thanks!
View 3 Replies
View Related
Feb 23, 2007
i am using visual web developer 2005 and SQL 2005 with VB as the code behindi am using INSERT command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString1").ToString() test.InsertCommandType = SqlDataSourceCommandType.Text test.InsertCommand = "INSERT INTO try (roll,name, age, email) VALUES (@roll,@name, @age, @email) " test.InsertParameters.Add("roll", TextBox1.Text) test.InsertParameters.Add("name", TextBox2.Text) test.InsertParameters.Add("age", TextBox3.Text) test.InsertParameters.Add("email", TextBox4.Text) test.Insert() i am using UPDATE command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ToString() test.UpdateCommandType = SqlDataSourceCommandType.Text test.UpdateCommand = "UPDATE try SET name = '" + myname + "' , age = '" + myage + "' , email = '" + myemail + "' WHERE roll 123 " test.Update()but i have to use the SELECT command like this which is completely different from INSERT and UPDATE commands Dim tblData As New Data.DataTable() Dim conn As New Data.SqlClient.SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True") Dim Command As New Data.SqlClient.SqlCommand("SELECT * FROM try WHERE age = '100' ", conn) Dim da As New Data.SqlClient.SqlDataAdapter(Command) da.Fill(tblData) conn.Close() TextBox4.Text = tblData.Rows(1).Item("name").ToString() TextBox5.Text = tblData.Rows(1).Item("age").ToString() TextBox6.Text = tblData.Rows(1).Item("email").ToString() for INSERT and UPDATE commands defining the command,commandtype and connectionstring is samebut for the SELECT command it is completely different. why ?can i define the command,commandtype and connectionstring for SELECT command similar to INSERT and UPDATE ?if its possible how to do ?please help me
View 2 Replies
View Related
Dec 12, 2005
I am using installshield to distribute SQL Server 2005 Express. I have the SQLEXPR.EXE file and I want to run it in /qb mode so the user can see the pretty dialogs pop up but not have to click anything.
View 6 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
Nov 4, 2006
Hi All,
i am using a OLE DB Source in my dataflow component and want to select rows from the source based on the Name I enter during execution time. I have created two variables,
enterName - String packageLevel (will store the name I enter)
myVar - String packageLevel. (to store the query)
I am assigning this query to the myVar variable, "Select * from db.Users where (UsrName = " + @[User::enterName] + " )"
Now in the OLE Db source, I have selected as Sql Command from Variable, and I am getting the variable, enterName,. I select that and when I click on OK am getting this error.
Error at Data Flow Task [OLE DB Source [1]]: An OLE DB error has occurred. Error code: 0x80040E0C.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for the command object.".
Can Someone guide me whr am going wrong?
myVar variable, i have set the ExecuteAsExpression Property to true too.
Please let me know where am going wrong?
Thanks in advance.
View 12 Replies
View Related
Sep 19, 2006
Hi. I am writing a program in C# to migrate data from a Foxpro database to an SQL Server 2005 Express database. The package is being created programmatically. I am creating a separate data flow for each Foxpro table. It seems to be doing it ok but I am getting the following error message at the package validation stage:
Description: An OLE DB Error has occured. Error code: 0x80040E0C.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for the command object".
.........
Description: "component "OLE DB Destination" (22)" failed validation and returned validation status "VS_ISBROKEN".
This is the first time I am writing such code and I there must be something I am not doing correct but can't seem to figure it out. Any help will be highly appreciated. My code is as below:
private bool BuildPackage()
{
// Create the package object
oPackage = new Package();
// Create connections for the Foxpro and SQL Server data
Connections oPkgConns = oPackage.Connections;
// Foxpro Connection
ConnectionManager oFoxConn = oPkgConns.Add("OLEDB");
oFoxConn.ConnectionString = sSourceConnString; // Created elsewhere
oFoxConn.Name = "SourceConnectionOLEDB";
oFoxConn.Description = "OLEDB Connection For Foxpro Database";
// SQL Server Connection
ConnectionManager oSQLConn = oPkgConns.Add("OLEDB");
oSQLConn.ConnectionString = sTargetConnString; // Created elsewhere
oSQLConn.Name = "DestinationConnectionOLEDB";
oSQLConn.Description = "OLEDB Connection For SQL Server Database";
// Add Prepare SQL Task
Executable exSQLTask = oPackage.Executables.Add("STOCK:SQLTask");
TaskHost thSQLTask = exSQLTask as TaskHost;
thSQLTask.Properties["Connection"].SetValue(thSQLTask, "oSQLConn");
thSQLTask.Properties["DelayValidation"].SetValue(thSQLTask, true);
thSQLTask.Properties["ResultSetType"].SetValue(thSQLTask, ResultSetType.ResultSetType_None);
thSQLTask.Properties["SqlStatementSource"].SetValue(thSQLTask, @"C:LPFMigrateLPF_Script.sql");
thSQLTask.Properties["SqlStatementSourceType"].SetValue(thSQLTask, SqlStatementSourceType.FileConnection);
thSQLTask.FailPackageOnFailure = true;
// Add Data Flow Tasks. Create a separate task for each table.
// Get a list of tables from the source folder
arFiles = Directory.GetFileSystemEntries(sLPFDataFolder, "*.DBF");
for (iCount = 0; iCount <= arFiles.GetUpperBound(0); iCount++)
{
// Get the name of the file from the array
sDataFile = Path.GetFileName(arFiles[iCount].ToString());
sDataFile = sDataFile.Substring(0, sDataFile.Length - 4);
oDataFlow = ((TaskHost)oPackage.Executables.Add("DTS.Pipeline.1")).InnerObject as MainPipe;
oDataFlow.AutoGenerateIDForNewObjects = true;
// Create the source component
IDTSComponentMetaData90 oSource = oDataFlow.ComponentMetaDataCollection.New();
oSource.Name = (sDataFile + "Src");
oSource.ComponentClassID = "DTSAdapter.OLEDBSource.1";
// Get the design time instance of the component and initialize the component
CManagedComponentWrapper srcDesignTime = oSource.Instantiate();
srcDesignTime.ProvideComponentProperties();
// Add the connection manager
if (oSource.RuntimeConnectionCollection.Count > 0)
{
oSource.RuntimeConnectionCollection[0].ConnectionManagerID = oFoxConn.ID;
oSource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(oFoxConn);
}
// Set Custom Properties
srcDesignTime.SetComponentProperty("AccessMode", 0);
srcDesignTime.SetComponentProperty("AlwaysUseDefaultCodePage", true);
srcDesignTime.SetComponentProperty("OpenRowset", sDataFile);
// Re-initialize metadata
srcDesignTime.AcquireConnections(null);
srcDesignTime.ReinitializeMetaData();
srcDesignTime.ReleaseConnections();
// Create Destination component
IDTSComponentMetaData90 oDestination = oDataFlow.ComponentMetaDataCollection.New();
oDestination.Name = (sDataFile + "Dest");
oDestination.ComponentClassID = "DTSAdapter.OLEDBDestination.1";
// Get the design time instance of the component and initialize the component
CManagedComponentWrapper destDesignTime = oDestination.Instantiate();
destDesignTime.ProvideComponentProperties();
// Add the connection manager
if (oDestination.RuntimeConnectionCollection.Count > 0)
{
oDestination.RuntimeConnectionCollection[0].ConnectionManagerID = oSQLConn.ID;
oDestination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(oSQLConn);
}
// Set custom properties
destDesignTime.SetComponentProperty("AccessMode", 2);
destDesignTime.SetComponentProperty("AlwaysUseDefaultCodePage", false);
destDesignTime.SetComponentProperty("OpenRowset", "[dbo].[" + sDataFile + "]");
// Create the path to link the source and destination components of the dataflow
IDTSPath90 dfPath = oDataFlow.PathCollection.New();
dfPath.AttachPathAndPropagateNotifications(oSource.OutputCollection[0], oDestination.InputCollection[0]);
// Iterate through the inputs of the component.
foreach (IDTSInput90 input in oDestination.InputCollection)
{
// Get the virtual input column collection
IDTSVirtualInput90 vInput = input.GetVirtualInput();
// Iterate through the column collection
foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
{
// Call the SetUsageType method of the design time instance of the component.
destDesignTime.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READWRITE);
}
//Map external metadata to the inputcolumn
foreach (IDTSInputColumn90 inputColumn in input.InputColumnCollection)
{
IDTSExternalMetadataColumn90 externalColumn = input.ExternalMetadataColumnCollection.New();
externalColumn.Name = inputColumn.Name;
externalColumn.Precision = inputColumn.Precision;
externalColumn.Length = inputColumn.Length;
externalColumn.DataType = inputColumn.DataType;
externalColumn.Scale = inputColumn.Scale;
// Map the external column to the input column.
inputColumn.ExternalMetadataColumnID = externalColumn.ID;
}
}
}
// Add precedence constraints to the package executables
PrecedenceConstraint pcTasks = oPackage.PrecedenceConstraints.Add((Executable)thSQLTask, oPackage.Executables[0]);
pcTasks.Value = DTSExecResult.Success;
for (iCount = 1; iCount <= (oPackage.Executables.Count - 1); iCount++)
{
pcTasks = oPackage.PrecedenceConstraints.Add(oPackage.Executables[iCount - 1], oPackage.Executables[iCount]);
pcTasks.Value = DTSExecResult.Success;
}
// Validate the package
DTSExecResult eResult = oPackage.Validate(oPkgConns, null, null, null);
// Check if the package was successfully executed
if (eResult.Equals(DTSExecResult.Canceled) || eResult.Equals(DTSExecResult.Failure))
{
string sErrorMessage = "";
foreach (DtsError pkgError in oPackage.Errors)
{
sErrorMessage = sErrorMessage + "Description: " + pkgError.Description + "";
sErrorMessage = sErrorMessage + "HelpContext: " + pkgError.HelpContext + "";
sErrorMessage = sErrorMessage + "HelpFile: " + pkgError.HelpFile + "";
sErrorMessage = sErrorMessage + "IDOfInterfaceWithError: " + pkgError.IDOfInterfaceWithError + "";
sErrorMessage = sErrorMessage + "Source: " + pkgError.Source + "";
sErrorMessage = sErrorMessage + "Subcomponent: " + pkgError.SubComponent + "";
sErrorMessage = sErrorMessage + "Timestamp: " + pkgError.TimeStamp + "";
sErrorMessage = sErrorMessage + "ErrorCode: " + pkgError.ErrorCode;
}
MessageBox.Show("The DTS package was not built successfully because of the following error(s):" + sErrorMessage, "Package Builder", MessageBoxButtons.OK, MessageBoxIcon.Information);
return false;
}
// return a successful result
return true;
}
View 2 Replies
View Related
May 9, 2008
I have a flat text file. All the columns are set to redirect on error. But, when I set it to row, it gives the error column, ErrColumn. Is there a way to display the real column name-the column which has the error.
Thanks.
View 6 Replies
View Related
Sep 16, 2006
Hello!I have problems with my SQL insert command..string zdroj = "server=LIBRA; database=ufd; uid=sa; password=555; ";string dotaz = "SELECT heslo FROM tbl_UFD_Uzivatelia WHERE meno='"+meno.Text+"'";SqlConnection conn = new SqlConnection(zdroj);SqlCommand sqlCmd = new SqlCommand(dotaz, conn);conn.Open(); SqlDataReader reader; reader = sqlCmd.ExecuteReader();reader.Read();Does something exist as oposite of DataReader? How can I execute insert command?Hope you will help me, thanx a lot
View 6 Replies
View Related
Sep 29, 2006
How to pass insert sqlquery using dataset
View 2 Replies
View Related
Jan 10, 2007
Hi guys! I have an INSERT INTO sqlcommand that inserts values into a table from another table. Why do I always get this error?
Violation of PRIMARY KEY constraint 'PK_TE_shounin_zangyou'. Cannot insert duplicate key in object 'dbo.TE_shounin_zangyou'.The statement has been terminated.
Here's the insert command...
Dim update_phase As New SqlCommand("INSERT INTO TE_shounin_zangyou (syain_No,date_kyou,time_kyou) SELECT syain_No,date_kyou,time_kyou FROM TE_zangyou WHERE [syain_No] = @syain_No", cnn)
The table where I would want to insert data is empty, but I get this error. What seems to be the problem?
Thanks.
Audrey
View 8 Replies
View Related
Jan 28, 2007
i want to include an insert command to enable admin user to insert new record through the grid view but for some reason the insert command doesnt work, and i want the page to just display the record of the restaurant name "Sakura Yeshi" but i cannot do that and i dont know why? Can somebody help me out with this? i know this might be easy but just i dont know how to deal with it. Thanks
My Code:<script runat="server">
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Dim resmen As String Dim connection As SqlConnection = New SqlConnection() connection.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionString").ToString()
resmen = ("SELECT resname,menu,price,date FROM rest_info WHERE resname = 'sakura yeshi'") Dim myCommand As SqlCommand = New SqlCommand(resmen, connection)
connection.Open() Dim dr As SqlDataReader = myCommand.ExecuteReader() While dr.Read() admingrid.DataBind() End While connection.Close() End Sub</script>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server"> <strong>Sakura Yeshi Admin Page<br /> <asp:GridView ID="admingrid" runat="server" AutoGenerateColumns="False" CaptionAlign="Left" DataSourceID="SqlDataSource1" Style="z-index: 100; left: 163px; position: absolute; top: 182px"> <Columns> <asp:CommandField ShowdeleteButton="True" ShowEditButton ="True" ShowinsertButton="True" /> <asp:BoundField DataField="resname" HeaderText="resname" ReadOnly="True" SortExpression="resname" /> <asp:BoundField DataField="menu" HeaderText="menu" SortExpression="menu" /> <asp:BoundField DataField="price" HeaderText="price" SortExpression="price" /> <asp:BoundField DataField="date" HeaderText="date" SortExpression="date" /> </Columns>
</asp:GridView> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT [resname], [menu], [price], [date] FROM [rest_info]" DeleteCommand="DELETE FROM [rest_info] WHERE [resname] = 'sakura yeshi'" InsertCommand="INSERT INTO [rest_info] ([resname], [menu], [price], [date]) VALUES (@resname, @menu, @date, @price)" UpdateCommand="UPDATE [rest_info] SET [resname] = @resname, [menu] = @menu, [price] = @price, [date] = @date WHERE [resname] = 'sakura yeshi'">
<InsertParameters> <asp:Parameter Name="resname" Type="Char" /> <asp:Parameter Name="menu" Type="char" /> <asp:Parameter Name="price" Type="Decimal" /> <asp:Parameter Name="date" Type="datetime" /> </InsertParameters> <UpdateParameters> <asp:Parameter Name="resname" Type="Char" /> <asp:Parameter Name="menu" Type="char" /> <asp:Parameter Name="price" Type="Decimal" /> <asp:Parameter Name="date" Type="datetime" /> </UpdateParameters> <DeleteParameters> <asp:Parameter Name="resname" Type="String" /> </DeleteParameters></asp:SqlDataSource></asp:Content>
View 8 Replies
View Related
Jul 18, 2007
Hi,
I am creating a page which has 5 text boxes.
Firstname, lastname, pass, zip, email
and a command button. now when i click this button i want it to use a stored procedure and insert the values from the text box to db table. i dont know what code to put in the command button.
View 3 Replies
View Related
Nov 11, 2005
I have three tables t1, t2, t3. I want to insert a new row in t1. I am using access 2000 with sql commands for the queries. There are 4 colums in t1. I know the values of two of the colums. The remaining two columns values are going to be look up in the other 2 tables, 1 value from t2 table and the other value from t3.. I need this to be in one statement. From what I have seen you can either use insert with values() or you can populate your table with unkown values from another table. But I want to do both, since 2 of my values are known and the other 2 need to come from another table.
I hope that I made it clear enough. Can anyone help me
Thanks
View 4 Replies
View Related
May 16, 2007
i am completely new to sql commands so i really need the help on this one.
I have three tables and they go like this
--User Table
firstTable->integerID
firstTable->stringName
--Group Table
secondTable->integerID
secondTable->stringName
--User to Groups table
thirdTable->integerFirstID
thirdTable->integerSecondID
thirdTable->stringDescription
the first table is has a user id and name and the second table has a group id and name, the third table keeps track of how many groups the user is assigned to. The problem i am having is writing a sql insert into command to add more entries to the third table from an asp.net 2.0 page, my sql command is incorrect.
The problem i am having i think, is that i am populating a dropdownlist with the names of all of the groups and when i select one that should be the group i am assigning to the user, the problem is, is that i dont know how to associated that dropdownlist string's name with its ID so that it can be passed to my insert command. is there a way to accomplish this all as a sql command? because i have a formview with a multiview and then a gridview under nested under all of that, and it would be a pain to write a event to work with all of the that.
if i do this it works
INSERT INTO ThirdTable (UserID,GroupID,Description) VALUES '318', '2', 'some description')
View 3 Replies
View Related
Mar 12, 2008
note: according to the product documentation parameterized queries should preview just fine.
reference: http://msdn2.microsoft.com/en-us/library/ms139904.aspx
i have an OLE DB Source
Data access mode: SQL command
i began with a simple query, and it previews just fine
i have an existing user variable in my package. its being referenced successfully by an intial "execute sql task" i have.
i then replace the where conditions with parameters.
i click on "parameters..." and map the parameters to my variables.
i click preview and an error appears.
the error message is...
There was an error displaying the preview
Additional information:
no value given for one or more required parameters. (Microsoft SQL Native Client)
any ideas or suggestions?
View 14 Replies
View Related
Apr 14, 2006
Hello all
please see this query
select * from Table1 where Table1ID in
(select Table1ID from Table3)
Step1 )select Table1ID from Table3
output->Giving error as Table1ID is not valid column in Table3 .
Step2)select * from Table1 where Table1ID in
(select Table1ID from Table3)
Output-> Giving all records of Table1 as i am expecting error from this query .
Please check with your demo database and reply.
Thanking you.
Ramana.
View 7 Replies
View Related
May 11, 2007
I am having trouble issuing a database command. Dim insertTopic As String = "INSERT forum_Topics " _
+ "(forum_id, topic_title, topic_poster, " _
+ "topic_type, topic_time) " _
+ "VALUES(@forum_id, @topic_title, @topic_poster," _
+ "@topic_type, @topic_time);UPDATE forum_Forums " _
+ "SET forum_topics = forum_topics + 1 " _
+ "WHERE forum_id = @forum_id" I have the forum_Topics table primary key enabled on topic_id. It is supposed to give it a topic_id number automatically, so I don't have to insert a topic_id I get this error message... Cannot insert the value NULL into column 'topic_id', table 'ASPNETDB.MDF.dbo.forum_Topics'; column does not allow nulls. INSERT fails.The statement has been terminated.Of course the collum does not allow NULLS, but I shouldn't have to insert a topic_id, because it is the primary key and it is the identity. What am I missing or doing wrong?
View 5 Replies
View Related
Oct 12, 2007
Hello all,
I am having problem to insert the record into database from sqldatasource control. my code is listed below, and i can't find anything why it cause the exception...
<asp:SqlDataSource ID="SqlDataSource1" runat="server" DataSourceMode="DataSet" ConnectionString="<%$ ConnectionStrings:WebsiteDataConnection %>" SelectCommand="SELECT * FROM [ServiceAgents]" InsertCommand="INSERT INTO ServiceAgents VALUES(@Ser_STATE, @Ser_CITY, @Ser_AGENT, @Ser_PHONE, @Ser_EQUIPMENT)"> <InsertParameters> <asp:FormParameter Name="Ser_STATE" FormField="TextBox_state"/> <asp:FormParameter Name="Ser_CITY" FormField="TextBox_city"/> <asp:FormParameter Name="Ser_AGENT" FormField="TextBox_agent"/> <asp:FormParameter Name="Ser_PHONE" FormField="TextBox_phone"/> <asp:FormParameter Name="Ser_EQUIPMENT" FormField="TextBox_equip" /> </InsertParameters> </asp:SqlDataSource>
The table has got the fields that needed for insert command, for example:
<table> <tr> <td>State:</td> <td> <asp:TextBox ID="TextBox_state" runat="server"></asp:TextBox> <asp:RequiredFieldValidator id="RequiredFieldValidator1" runat="server" ControlToValidate="TextBox_state" Display="Static" ErrorMessage="Please enter a state." /> </td> </tr> <tr> <td>City:</td> <td> <asp:TextBox ID="TextBox_city" runat="server"></asp:TextBox> <asp:RequiredFieldValidator id="RequiredFieldValidator2" runat="server" ControlToValidate="TextBox_city" Display="Static" ErrorMessage="Please enter a city." /> </td> </tr> <tr> <td>Agent:</td> <td> <asp:TextBox ID="TextBox_agent" runat="server"></asp:TextBox> <asp:RequiredFieldValidator id="RequiredFieldValidator3" runat="server" ControlToValidate="TextBox_agent" Display="Static" ErrorMessage="Please enter a agent." /> </td> </tr> <tr> <td>Phone:</td> <td> <asp:TextBox ID="TextBox_phone" runat="server"></asp:TextBox> <asp:RequiredFieldValidator id="RequiredFieldValidator4" runat="server" ControlToValidate="TextBox_phone" Display="Static" ErrorMessage="Please enter a phone No." /> </td> </tr> <tr> <td>Equipment:</td> <td> <asp:TextBox ID="TextBox_equip" runat="server"></asp:TextBox> <asp:RequiredFieldValidator id="RequiredFieldValidator5" runat="server" ControlToValidate="TextBox_equip" Display="Static" ErrorMessage="Please enter a equipment." /> </td> </tr> <tr> <td></td> <td> <asp:Button ID="Button2" runat="server" Text="Add" OnClick="addEntry" BackColor="#FFFBFF" BorderColor="#CCCCCC" BorderStyle="Solid" BorderWidth="1px" Font-Names="Verdana" Font-Size="1.0em" ForeColor="#284775"/> </td> </tr> </table>
And in the code i just called: SqlDataSource1.Insert(); Then the page gives me the exception like:
Cannot insert the value NULL into column 'STATE', table 'WebsiteData.dbo.ServiceAgents'; column does not allow nulls. INSERT fails. The statement has been terminated.
But i actually input all the required text in the textbox.... Any idea guys?
Thanks
View 2 Replies
View Related
Nov 20, 2007
I get the following error, when I try to create a company. Any help would be appreciated. Error Code: Cannot insert the value NULL into column 'CompanyName', table 'Telemetry.dbo.Company'; column does not allow nulls. INSERT fails.The statement has been terminated. Here is my aspx file: <table> <tr ><td style="width:110px"><b>Company Name:</b></td><td ><asp:TextBox ID="CompanyName" Text="" runat="server" width="250px"/></td></tr> <tr><td ><b>Phone Number:</b></td><td ><asp:TextBox runat="server" ID="CompanyPhone" Text="" Width="250px"/></td></tr> <tr><td ><b>Company E-mail:</b></td><td ><asp:TextBox runat="server" ID="CompanyEmail" Text="" Width="250px"/></td></tr> <tr><td ><b>Street Address:</b></td><td><asp:TextBox runat="server" ID="AddressStreet" Text="" Width="250px"/></td></tr> <tr><td ><b>City :</b></td><td><asp:TextBox runat="server" ID="AddressCity" Text="" Width="200px"/></td></tr> <tr><td ><b>State/Province:</b></td><td><asp:TextBox runat="server" ID="AddressState" Text="" Width="150px"/></td></tr> <tr><td ><b>Zip Code:</b></td><td><asp:TextBox runat="server" ID="AddressZip" Text="" Width="150px"/></td></tr> <tr></tr> </table> <asp:Button ID="Submit" runat="server" OnClick="Submitinfo" /> <br /> <asp:SqlDataSource ID="sqlCreateCompany" runat="server" ConnectionString="<%$ ConnectionStrings:SqlServer1 %>" InsertCommand="INSERT INTO Company(CompanyName, CompanyPhone, CompanyEmail, AddressStreet, AddressCity, AddressState, AddressZip) VALUES (@CompanyName, @CompanyPhone, @CompanyEmail, @AddressStreet, @AddressCity, @AddressState, @AddressZip)" SelectCommand="SELECT [CompanyID], [CompanyName], [CompanyPhone], [CompanyEmail], [AddressStreet], [AddressZip], [AddressState], [AddressCity] FROM [Company]" > <InsertParameters> <asp:FormParameter Name="CompanyName" FormField="CompanyName"/> <asp:FormParameter Name="CompanyPhone" FormField="companyPhone" /> <asp:FormParameter Name="CompanyEmail" FormField="CompanyEmail" /> <asp:FormParameter Name="AddressStreet" FormField="AddressStreet" /> <asp:FormParameter Name="AddressCity" FormField="AddressCity" /> <asp:FormParameter Name="AddressState" FormField="AddressState" /> <asp:FormParameter Name="AddressZip" FormField="AddressZip" /> </InsertParameters> </asp:SqlDataSource> .....Behind the code.............. protected void Submitinfo(object sender, EventArgs e) { //TextBox t = (TextBox)FormView1.FindControl("CompanyName"); sqlCreateCompany.Insert(); } .........Database Company Table Design..............CompanyID int UncheckedCompanyName varchar(100) UncheckedCompanyPhone varchar(50) CheckedCompanyEmail varchar(100) CheckedAddressStreet varchar(100) CheckedAddressCity varchar(50) CheckedAddressState varchar(2) CheckedAddressZip varchar(5) CheckedCompanyLogo varchar(100) Checked
View 2 Replies
View Related
Dec 19, 2007
i have a question if anyone can help please..
i have need to create a database table as a go-between, where users will upload info. then on a backend page admin will make some data settings, then transfer it to the proper, permanent tables.(all this to avoid expecting users themselves to upload files to proper places).
can i use a basic SQL INSERT statement to move from one table directly to another without the use of parameters? for example,
INSERT INTO tblOne (fieldOne,fieldTwo,fieldThree) VALUES (tblTwo(fieldOne,fieldTwo,fieldThree)
if so, what is the syntax? and if not, then what would i have to do, just use some go between variables or objects? perhaps a DataSet to pull the values, then use same DataSet to load the values into new table, then a simple DELETE statement on the intermediate table?
i mean, i can work my way through this, but if anybody has gone through a similar situation i sure could use any pointers or tips to keep it as clean and simple as possible.
thanks all!
View 3 Replies
View Related
Mar 21, 2008
Hey people im just wondering if someone could help me out with this Insert query im doing from one of the learn asp videos. I have a table called EquipmentBooking and it contains the following fields
<teachingSession, int,> <staff, char(5),> <equipment, varchar(15),> <bookedOn, datetime,> <bookedFor, datetime,> now im doing the following Insert statement in Visual Studio 2005 when the submit button is clicked but all I get is the catched error exception and I just can working out why. Can someone help? heres the code im using
Dim WebTimetableDataSource As New SqlDataSource()WebTimetableDataSource.ConnectionString = ConfigurationManager.ConnectionStrings("WebTimetableConnectionString").ToString()
WebTimetableDataSource.InsertCommandType = SqlDataSourceCommandType.Text
WebTimetableDataSource.InsertCommand = "INSERT INTO EquipmentBooking (teachingSession, staff, equipment, bookedOn, bookedFor) VALUES (@TeachingDropDown, @StaffDropDown, @EquipmentDropDown, @DateTimeStamp, @DateTextBox)"WebTimetableDataSource.InsertParameters.Add("TeachingDropDown", TeachingDropDown.Text)
WebTimetableDataSource.InsertParameters.Add("StaffDropDown", StaffDropDown.Text)WebTimetableDataSource.InsertParameters.Add("EquipmentDropDown", EquipmentDropDown.Text)
WebTimetableDataSource.InsertParameters.Add("DateTimeStamp", DateTime.Now())WebTimetableDataSource.InsertParameters.Add("DateTextBox", DateTime.Now())
Dim rowsAffected As Integer = 0
Try
rowsAffected = WebTimetableDataSource.Insert()Catch ex As Exception
Server.Transfer("problem.aspx")
Finally
WebTimetableDataSource = Nothing
End Try
If rowsAffected <> 1 ThenServer.Transfer("confirmation.aspx")
End If
View 5 Replies
View Related
Jan 24, 2004
This is a real head ache. Nothing I do to add a record to my SQL2k Database wil work.
I'm logged into it as "sa".
I've Tried Stored Procedures:
Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim myCommand As New SqlCommand("AddLender", myConnection)
' Mark the Command as a SPROC
myCommand.CommandType = CommandType.StoredProcedure
Dim parameterUserName As New SqlParameter("@UserName", SqlDbType.NVarChar, 100)
parameterUserName.Value = userName
myCommand.Parameters.Add(parameterUserName)
Dim parameterName As New SqlParameter("@Name", SqlDbType.NVarChar, 100)
parameterName.Value = name
myCommand.Parameters.Add(parameterName)
Dim parameterCompany As New SqlParameter("@Company", SqlDbType.NVarChar, 100)
parameterCompany.Value = Company
myCommand.Parameters.Add(parameterCompany)
Dim parameterEmail As New SqlParameter("@Email", SqlDbType.NVarChar, 100)
parameterEmail.Value = email
myCommand.Parameters.Add(parameterEmail)
Dim parameterContact As New SqlParameter("@Contact", SqlDbType.NVarChar, 100)
parameterContact.Value = contact
myCommand.Parameters.Add(parameterContact)
Dim parameterPhone As New SqlParameter("@Phone", SqlDbType.NVarChar, 100)
parameterPhone.Value = Phone
myCommand.Parameters.Add(parameterPhone)
Dim parameterFax As New SqlParameter("@Fax", SqlDbType.NVarChar, 100)
parameterFax.Value = Fax
myCommand.Parameters.Add(parameterFax)
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
Return CInt(parameterItemID.Value)
The Sproc.......
CREATE PROCEDURE AddLender
(
@Username nvarchar(100),
@ModuleID int,
@Email nvarchar(100),
@Name nvarchar(100),
@Rep nvarchar(250),
@Phone nvarchar(250),
@Fax nvarchar(250),
@City nvarchar (100),
@State nvarchar(100),
@ItemID int OUTPUT
)
AS
INSERT INTO Lenders
(
Email,
Name,
Rep,
Phone,
Fax,
CIty,
State
)
VALUES
(
@Email,
@Name,
@Rep,
@Phone,
@Fax,
@City,
@state
)
SELECT
@ItemID = @@Identity
GO
I get no Errors... I've run SQLProfiller and I don;t even see it run...
I also tried the method..
Dim strSql As String
Dim objDataSet As New DataSet()
Dim objConnection As OleDbConnection
Dim objAdapter As OleDbDataAdapter
strSql = "Select ItemId,ModuleId,Name,Rep, Email,Phone,Fax,City,State,Address From Portal_Lenders;"
objConnection = New OleDbConnection(ConfigurationSettings.AppSettings("ConnectionStringOledb"))
objAdapter = New OleDbDataAdapter(strSql, objConnection)
objAdapter.Fill(objDataSet, "Lenders")
Dim objtable As DataTable
Dim objNewRow As DataRow
objtable = objDataSet.Tables("Lenders")
objNewRow = objtable.NewRow()
objNewRow("ModuleId") = moduleId
objNewRow("Name") = NameField.Text
objNewRow("Rep") = RepField.Text
objNewRow("Email") = EmailField.Text
objNewRow("Phone") = PhoneField.Text
objNewRow("Fax") = FaxField.Text
objNewRow("City") = CityField.Text
objNewRow("State") = Statefield.Text
objNewRow("Address") = StreetAddress.Text
objtable.Rows.Add(objNewRow)
Still no Error or activity in the Pofiller.
Please Help...
View 2 Replies
View Related
Jan 12, 2006
Good morning,
I don't know where is the problem here, but I can not add a new record in the SQL database related. Can you take a look at the code please and let me know what's wrong? When I press the button to insert the data is automatically reloads without any input in the database.
Thanks in advance,Alejandro.
<%@ Page Language="VB" Debug="true" %>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head>
<title>Untitled Page</title>
</head>
<body>
<form id="form1" method="post" runat="server">
<asp:SqlDataSource id="ds1" runat="server"
ConnectionString="server=62.149.153.13;database=MSSql13067;uid=MSSql13067;pwd=d7f15bd2"
SelectCommand="SELECT [usuario], [emailes] FROM [usuarioyemail]"
InsertCommand="INSERT INTO [usuarioyemail] ([usuario], [emailes]) VALUES (@usuariocontrol, @emailcontrol)"
>
<InsertParameters>
<asp:ControlParameter Name="usuariocontrol" ControlID="usuario" Type="Char" PropertyName="Text" />
<asp:ControlParameter Name="emailcontrol" ControlID="email" Type="Char" PropertyName="Text" />
</InsertParameters>
</asp:SqlDataSource>
<asp:TextBox ID="usuario" runat="server"></asp:TextBox>
<asp:TextBox ID="email" runat="server"></asp:TextBox>
<asp:Button ID="Button1" runat="server" Text="Button" />
</form>
</body>
</html>
View 3 Replies
View Related
May 21, 2001
I need to move various records from a production database to an archive database. The fields in the two databases are identical but the archive database has an additional "Transfer Date" field. Can anyone recommend an easy way to create an SQL statement to move the record from production to archive? I've tried
INSERT INTO ARCHIVE (SELECT * FROM PRODUCTION WHERE ACCOUNTNO='XYZ')
but I get an error saying the columns among the two tables do not match (obviously because of the "Transfer Date" column). Is there a way to use a similar SQL statement that will populate the "Transfer Date" column w/ today's date?
Thanks in advance for your help.
View 2 Replies
View Related
Feb 24, 2008
Hello,
I am having a strange problem with SQL server 2000.
Basically I am trying to insert data into only one field within a table that has two fields.
--------------
field1 | field2 |
--------------
if i use the statement:
Code:
INSERT INTO tableName (field2) VALUES ('value')
I am greeted by the error messgae: The statement has been terminated.
However If I try it this way:
Code:
IINSERT INTO tableName VALUES ('','value')
The statement works fine.
I am unable to enter data by referencing the field name explicitly. Has anyone ever seen this before?
View 3 Replies
View Related
Sep 22, 2005
I'm trying to insert into a table 2 values one of which is an
exec master..xp_cmdshell @command where I have assigned @command with a value
this statement gives me the result into a 1 col. table fine:
insert into mytable99(col1) exec master..xp_cmdshell @command
now what I want to do is put col2 in there as well!!
ie. insert into mytable99(col1,col2) values (exec master..xp_cmdshell @command, '123')
I get a sytax error ... on the exec ??
Could anyone help re the proper way of doing this ... thanks in advance
View 1 Replies
View Related
Oct 13, 2005
I use a similar command below to insert into a temp table the result of a large command line call to an exectable with many parameters passed in the command of which the result passed back contains many items. I then parse the response string to get my results...
set @command = 'dir'
insert into tsverisign(response) exec master..xp_cmdshell @command
My question is our can I insert two values at the same time to this same table one of which is my "exec master..xp_cmdshell @command"
similar to insert into tables (field_a, feild_b) values ('1','2')
Something like (and I know this does not work):
insert into tsverisign(response,trans_id)
values (exec master..xp_cmdshell @command, '123')
Any help would be greatly appreciated .... PS I'm new to MS SQL 2000 and proper syntax etc. etc. so I need full example so I can try. :rolleyes:
View 1 Replies
View Related
Oct 17, 2013
I have a table with a column called SortOrder. It's an integer. The table also has a name and a city.I want to do an Insert and fill in SortOrder with the next higher integer, of all rows with city='NY'
Insert (name,city,SortOrder) values ('Dave','NY',
select max(SortOrder)+1 from myTable where city='NY')
View 3 Replies
View Related
Oct 13, 2005
I am trying to use the 'Bulk Insert' command to load a data file into aMS-SQL db. The line I am using is:Bulk Insert SVC_Details From "C:XFILE.TXT" With (FieldTerminator = ',')I have tried the file name with " around it and with ' around it and boththe " and ', but every time the Query Analyzer comes back with the followingerror:---Begin Error Msg---Server: Msg 4861, Level 16, State 1, Line 1Could not bulk insert because file ''C:XFILE.TXT'' could not be opened.Operating system error code 123(The filename, directory name, or volumelabel syntax is incorrect.).---End Msg---I am not sure what is wrong with this line. Can any one tell me what iswrong?Mikem charney at dunlap hospital dot org
View 1 Replies
View Related
Aug 29, 2006
Hi,
I'm stuck on the following thing:
After a slowly changing dimension task I replaced the OLE DB Destination task by an OLE DB Command and created the insert manually. This because I need to work further on the dataset. So I do a union all between the output of the two OLE DB Commands (insert and update). Untill here no problem. But than, because I need an ID further on, I do a lookup in the table in which I just inserted and updated my data for the right ID's. When I run this project I get the error message "row yielded no match during lookup".
I don't understand this beacause I just inserted the data and I've checked, it's there.
I could resolve this by splitting up in two control flows (reselect all the needed data wíth the ID-field in my selection) but I would prefer to solve it in another way
Greets,
Tom
View 12 Replies
View Related
Oct 16, 2006
I am working from C# VS2005 environment. My databases are in SQLEXPRESS. All tables have a unique Primary Key field. The key is just one field of int value that has seed 1 and autoIncrement 1. My question concerns a Stored Procedure that I just created. If "executed" fine. I did not get any compillation errors
My question is: if I called this procedure from C# code with paramaters I specified will it create a NEW record for me which is my intent? I did not specify the Primary Key field value: it is not among parameters. I hope that when the first record is created it will be seeded with value = 1 and the subsequent records will be autoincremented by 1 each time. Am I right?
If not then how shall I specify the Primary Key value inside the Stored Procedure? I usually manipulate tables from the code but today I decided to master the stored procedure technique since it offers many advantages for my situation now.
My stored procedure follows. Please review, maybe someone can offer suggestions for improving such things as procName Type variable or anything else.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[InsertRows]
@bid int,
@ask int,
@last int,
@volume int,
@dateTimed DateTime,
@procName varchar
AS
BEGIN
SET NOCOUNT ON;
INSERT procName (
bid,
ask,
last,
volume,
dateTimed)
Values (
@bid,
@ask,
@last,
@volume,
@dateTimed)
END.
Many thanks in advance.
View 9 Replies
View Related
Mar 14, 2008
I am in need of some help. We have a database that has two tables, one that holds a computers information and the other that holds model numbers that computers have.
The tables are set up like this:
tbl_comp: comp_ID (identity), comp_model_ID (num), comp_desc (Char)
tbl_model: model_ID (identity), mod_modelName (Char)
I can perform an INSERT command to enter a computer, but in order for it to reference the Model Number table, I have to use the mod_ID field. (I think it is best practice to use the ID field when linking tables)
I want to be able to do the INSERT command, but use the modelName instead. So I think I need to do some kind of lookup, where I pass in the modelName, but it inserts in the modelID.
However, I do not know what to call this procedure, and therefore I cannot search for help on google.
Any help is appreciated. If I am unclear on my question, please let me know.
Thanks,
John Dombrowski
View 5 Replies
View Related
