Creating An SQL Connection String And Inserting New Rows.
Jun 15, 2006
I am ultra new to this so thanks in advance for any help.
I was trying to create a connection to a database that I created in SQL Express. I am essentially trying to submit three attributes to the existing database from a table that consists of three textboxes and a submit button. I would like all of the code to be in the head of the page (because that is the standard here) so I wanted to know what the connection string should be in Visual Basic 2005 Express to establish a connection on the same machine. I'm not sure about the connection string, but I am also not sure about a lot of the code. Also, the Using clauses seem to give me an error (where should it go?). This is what I have in the head of the page (visual C# by the way). Also, I got this from http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson03.aspx :
We have a mobile device project, which has a database file (Database.sdf) as part of the project. I am trying to connect to it to insert a record. The syntax I'm using is:
I have tried various connection strings such as "data source=MyDocumentsDatabase.sdf"; and "data source=C:MyProjectsThisParticularProjectDatabase.sdf"; << the connection string in teh server explorer
and "data source=MyAssemblyName.Database.sdf"; and "data source=MyAssemblyNameDatabase.sdf";
I've also tried getting the path the the executing assembly and adding that in, but still no luck.
but they all throw the error: The database file cannot be found. Check the path to the database. [ File name = Database.sdf ]
I have searched the internet and found no explanation about how the path to the Database is made up (ie is it the path on *my* computer, or the virtual mobile device?) and how I can actually get the path the the database without hardcoding it.
I have a SQL script to insert data into a table as below:
INSERT into [SRV1INS2].BB.dbo.Agents2 select * from [SRV2INS14].DD.dbo.Agents
I just want to set a Trigger on Agents2 Table, which could delete all rows in the table , before carry out any Insert operation using above statement.I had below Table Trigger on [SRV1INS2].BB.dbo.Agents2 Table as below: But it did not perform what I intend to do.
USE [BB] GO /****** Object: Trigger Script Date: 24/07/2015 3:41:38 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
I added a connection (ADO.NET) object by name testCon in the connection manager - I wanted to programmatically supply the connection string. So I used the "Expressions" property of the connection object and set the connectionstring to one DTS variable. The idea is to supply the connection string value to the variable - so that the connection object uses my connection string.
Then I added a "Backup Database Task" to my package with the name BkpTask. Now whenever I try to set the connection property of BkpTask to the testCon connection object, by typing testCon, it automatically gets cleared. I am not able to set the connection value.
Then after spending several hours I found that this is because I have customized the connection string in testCon. If I don't customize the connection string, I am able to enter the "testCon" value in the connection property of the BkpTask.
I have written following SQL query, this creates temporary table, inserts rows into it. I need to create VIEW "vw_NumberOfAttachments" in the database. I initially created table using "CREATE TABLE" but then i got error as VIEW can not be filled by temporary table. Hence I am using DECLARE TABLE -------------------------------- SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE sp_GetViewNumberOfAttachments -- Add the parameters for the stored procedure here
AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
DECLARE @emailMessageID bigint DECLARE @metaDataStorageID bigint DECLARE @numberOfAttachments int
DECLARE ATTACHMENT_CURSOR CURSOR FOR SELECT emailMessageID, metaDataStorageID FROM ppaEmailMessage WHERE hasAttachments='true'
OPEN ATTACHMENT_CURSOR FETCH NEXT FROM ATTACHMENT_CURSOR INTO @emailMessageID, @metaDataStorageID
WHILE @@FETCH_STATUS = 0 BEGIN -- here the table name need to get dynamically the name of the attachment table -- for a moment it is written as ppaMsOfficeDoc, but that should change dynamically set @numberOfAttachments = (SELECT count(*) FROM ppaMsOfficeDoc WHERE metaDataStorageID = @metaDataStorageID)
INSERT INTO @AttachmentDetails(emailMessageID, metaDataStorageID, numberOfAttachments) VALUES (@emailMessageID, @metaDataStorageID, @numberOfAttachments)
FETCH NEXT FROM ATTACHMENT_CURSOR INTO @emailMessageID, @metaDataStorageID END
CLOSE ATTACHMENT_CURSOR DEALLOCATE ATTACHMENT_CURSOR
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'vw_NumberOfAttachments') DROP VIEW vw_NumberOfAttachments GO CREATE VIEW vw_NumberOfAttachments AS SELECT @AttachmentDetails.emailMessageID, @AttachmentDetails.metaDataStorageID, @AttachmentDetails.numberOfAttachments FROM @AttachmentDetails GO END GO
----------------------
I am getting following errors: ----------- Msg 102, Level 15, State 1, Procedure sp_GetViewNumberOfAttachments, Line 57 Incorrect syntax near 'vw_NumberOfAttachments'. Msg 137, Level 15, State 2, Procedure vw_NumberOfAttachments, Line 3 Must declare the scalar variable "@AttachmentDetails". Msg 102, Level 15, State 1, Line 2 Incorrect syntax near 'END'. ----------- Can anyone please suggest whats wrong in there? Many thanks
I have created a table with the following columns...Date(datetime),Actual (Int),Planned (Int)I need to insert weekending dates starting from 23/04/04 loopingthru'for the next 52weeks automatically into the date column.Then in the actual and planned colums, I need to insert a count ofsome records in the table.I will appreciate help on a SQL query to achieve this!
I can't see what is going on, this is the situation:
I call the Pull method, specify the table to be affected, the query to be used, the connection string to the remote SQL server, the tracking options (On) and the Error table. The pull method executes with no errors however, no table is ever created. I don't know why, here's what I have done so far:
I read the SQL BOOKS ONLINE help on preparing RDA, I set up the IIS virtual directory for anonymous access and on the connection string I send in the user name and password for the SQL server, I went into the SQL Server and grated access to the user name to the database that I am going to access and I made the user a db_owner.
So, according to SQL BOOKS ONLINE I have everything right however, it won't populate, so right now I am open to suggestions on how to get this to work, heres the code: ------------------------------------------------------------------------------------------------------------------ string rdaOleDbConnectString = "Provider=SQLOLEDB;Data Source=<Server>;Initial Catalog=<DB>; User Id=<User>;Password=<Password>"; (it's not exactly like this, but in it has the proper values) string connectionString = "Data Source="\Program Files\client\db\MobileDB.sdf"";
SqlCeRemoteDataAccess rda = new SqlCeRemoteDataAccess("http://10.1.1.206/mobile/sqlcesa30.dll", connectionString);
IList _tableNames = new ArrayList(); IList _queries = new ArrayList();
############ Code that prepares tables and queries ############
I have an item table (#Codes) which contains itemIDs, some of which are always used in a subsequent data table (#Data) to hold information about a case+scearnio. These item rows have a standarditem value = 1. Rows in the item table which may be included in the subsequent data table (#Data), but not for every single case+scearnio, are considered nonstandard rows and therefore have a standarditem value = 0. I have been trying to write some code that automatically inserts rows from #Codes which have standarditem = 1 into #Data for each of the caseID+scenarioIDs in #Cases but have not figured it out yet. See "desired result" below to see exactly the output I'm trying to achieve.
Also, is there a way to insert the standarditem = 1 rows from #Codes into #Data when new caseID+scenarioID rows are added to #Cases? Ultimately, I'd like to either create a button for the user to click in the Access interface that inserts these rows when clicked OR when the user enters a new caseID+scenarioID in #Cases the rows just automatically appear for the new case row. This way, the rows will already have the caseID, scenarioID & itemID fields already populated and all the user will have to do is enter the item value and be able to manually add standarditem = 0 rows and their values if needed.
Code Block
create table #Codes (itemID nvarchar(2), standarditem int) insert into #Codes values (1,1) insert into #Codes values (2,0) insert into #Codes values (3,1) insert into #Codes values (4,1) insert into #Codes values (5,0)
create table #Cases (caseID nvarchar(5), scenarioID nvarchar(15), createdate datetime) insert into #Cases values (823, 1, '20071210') insert into #Cases values (823, 2, '20071211') insert into #Cases values (824, 1, '20071213')
I have an XML string that I get by using a query similar to the following....
SELECT 1 AS Tag, NULL AS Parent, NULL AS [Zip_ROOT!1!], NULL AS [Zip!2!Value!element], NULL AS [Zip!2!State!element] FROM ZipCodes UNION SELECT 2, 1, NULL, Zip, State FROM ZipCodes where id < 3 FOR XML EXPLICIT
This returns an XML string, which is very long. I need to take this string and INSERT/UPDATE to a Text column in a table.
Any kind of help in this regard will be grately appreciated.
Hi again, I am having troubles inserting a datetime value in a table to a string. what iw ant to do is have it be sent in an email. its an attendance email. here is the code i have right now:
Folks, this isn't exactly a 'Getting Started' question, but I couldn't find a more appropriate Application Development forum.
I'm porting an open source PHP application (http://sourceforge.net/projects/gallery) to use SQL Server as a backend. One of Gallery's unit test scripts tests the ability to insert a string containing a NULL character ( ). It's OK if the string is truncated during insertion, just so long as everything before the is there.
The string being inserted looks like:
$testString = "The NULL character should be escaped !";
(Note the between "escaped " and " !")
The error that the Gallery test script is getting is:
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'The NULL character should be escaped '.] in EXECUTE("INSERT INTO g2_PluginParameterMap (g_pluginType, g_pluginId, g_itemId, g_parameterName, g_parameterValue) VALUES ('module','unitTestModule',1,'test19476','The NULL character should be escaped !')")
It looks like SQL Server is complaining about the syntax. I've written a much simpler test script in the hopes of reproducing the problem, but I don't know if what I'm now hitting is the same problem or a different one.
// Connect to the db $db = new COM("ADODB.Connection") or die("Cannot start ADO"); $db->open($connectString);
// Drop & recreate the table $db->Execute ($sqlTableDrop); $db->Execute ($sqlTableCreate);
// Insert the test data //$testString = "The NULL character should be escaped !"; $testString = "This is a test string."; $res = $db->Execute("insert into ljmtemp (col1) values ('$testString')"); if (!$res) die ("INSERT failed");
// Disconnect from the db $db->Close();
?>
And it results in:
C:MyServer>php testMsSqlInsertNull.php PHP Fatal error: Uncaught exception 'com_exception' with message 'Source: Microsoft OLE DB Provider for ODBC Drivers Description: [Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark after the character string 'The NULL character should be escaped '.' in C:MyServer estMsSqlInsertNull.php:27 Stack trace: #0 C:MyServer estMsSqlInsertNull.php(27): com->Execute('insert into ljm...') #1 {main} thrown in C:MyServer estMsSqlInsertNull.php on line 27
I'm not sure if this is the same problem as Gallery is reporting or another one.
It looks like somebody is treating the as a string terminator, but when i double the backslash the literal '