Adding String To Database, But Name Of String Is Added, Not Data
Mar 12, 2008
Hello, I am tring to add a string my database. Info is added, but it is the name of the string, not the data contained within. What am I doing wrong? The text "Company" and "currentUserID" is showing up in my database, but I need the info contained within the string. All help is appreciated!
Imports System.Data
Imports System.Data.Common
Imports System.Data.SqlClientPartial Class _DefaultInherits System.Web.UI.Page
Protected Sub CreateUserWizard1_CreatedUser(ByVal sender As Object, ByVal e As System.EventArgs) Handles CreateUserWizard1.CreatedUser
'Database ConnectionDim con As New SqlConnection("Data Source = .SQLExpress;integrated security=true;attachdbfilename=|DataDirectory|ASPNETDB.mdf;user instance=true")
'First Command DataDim Company As String = ((CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Company"), TextBox)).Text)
Dim insertSQL1 As StringDim currentUserID As String = ((CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("UserName"), TextBox)).Text)
insertSQL1 = "INSERT INTO Company (CompanyName, UserID) VALUES ('Company', 'currentUserID')"Dim cmd1 As New SqlCommand(insertSQL1, con)
'2nd Command Data
Dim selectSQL As String
selectSQL = "SELECT companyKey FROM Company WHERE UserID = 'currentUserID'"Dim cmd2 As New SqlCommand(selectSQL, con)
Dim reader As SqlDataReader
'3rd Command Data
Dim insertSQL2 As String
insertSQL2 = "INSERT INTO Company_Membership (CompanyKey, UserID) VALUES ('CompanyKey', 'currentUserID')"Dim cmd3 As New SqlCommand(insertSQL2, con)
'First CommandDim added As Integer = 0
Try
con.Open()
added = cmd1.ExecuteNonQuery()
lblResults.Text = added.ToString() & " records inserted."Catch err As Exception
lblResults.Text = "Error inserting record."
lblResults.Text &= err.Message
Finally
con.Close()
End Try
'2nd Command
Try
con.Open()
reader = cmd2.ExecuteReader()Do While reader.Read()
Dim CompanyKey = reader("CompanyKey").ToString()
Loop
reader.Close()Catch err As Exception
lbl1Results.Text = "Error selecting record."
lbl1Results.Text &= err.Message
Finally
con.Close()
End Try
'3rd Command
Try
con.Open()
added = cmd3.ExecuteNonQuery()
lbl2Results.Text = added.ToString() & " records inserted."Catch err As Exception
When I execute this, it works ok but only one the first character of the request.form["d"] is stored to the db.I checked the sproc with another routine and it adds full data, and I've verified that value of request.form["d"] is longer than one chaacter by printing it to the page. Anyone got any ideas why only the first char is getting added to the db?? SqlConnection SqlConnection1 = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString1"].ConnectionString); SqlCommand SqlCommand1 = new SqlCommand("addRoute", SqlConnection1); SqlCommand1.CommandType = CommandType.StoredProcedure; SqlParameter SqlParameter1 = SqlCommand1.Parameters.Add("@ReturnValue", SqlDbType.Int); SqlParameter1.Direction = ParameterDirection.ReturnValue; SqlCommand1.Parameters.Add("@xmlData", Request.Form["d"]); SqlConnection1.Open(); SqlCommand1.ExecuteNonQuery(); Response.Write(SqlCommand1.Parameters["@ReturnValue"].Value); //Response.Write(Request.Form["d"]);
I'm not a programmer by trade, so please be gentle with me! I am trying to add or insert data to the beginning of an existing string of data. The string is in one field in a table, with various options seperated by [ ]. An example of the current data is: [Eye Color:TEST][Hair Color:TEST] and I would like to add the data [# Persons:1] to the beginning of the string so it looks like this: [# Persons:1][Eye Color:TEST][Hair Color:TEST]
I am trying to insert a row into a table of Microsoft SQL Server 2000.
There are various columns.
[SNO] [numeric](3, 0) NOT NULL , [DATT] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [DATTA] [char] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [CODECS] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
The [DATTA] column is causing a problem. Even if I am trying to put only 1700 character string into [DATTA], the java code throws the following exception:-
StaleConnecti A CONM7007I: Mapping the following SQLException, with ErrorCode 0 and SQLState 08S01, to a StaleConnectionException: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Connection reset
at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
Why is it throwing an exception even though the sum-total of this row doesn't exceed 8000 characters?
hihere is a problem:i have a databes with many, many tablesthe problem is that i dont know where 'abcd' string is (but it is for surein one of that table)is there any SELECT that could help me with finding this string in database?--greets
Hi, i'm writing a SOCKET Port Listener for a Database, it must be multi-threaded and listen on a port for a record that when it comes in, it must write the record to the SQL database (MS SQL Server). I've got the listener to read the data over the port already and write the record into a string which i have already sliced up. Now i need to create a connection to the database and insert the variables into the database.
If Someone will please be able to give me a rough idea of how i could accomplish this with some sample code, then i will be greatful, i'm new to C#, but here is my code that i have so far.
//This is the Connection that i have made and where i am currently stuck, i dunno how to go further. Any help will be welcome.
public class ConnectionToMSDatabase { public void InsertDataIntoDatabase(string TableName, string connectionString, string dataFields) { string InsertSQLStatement; InsertSQLStatement = "INSERT INTO " + TableName + " VALUES (" + dataFields + ")";
Basically I need to insert underscore in between a string when there is a space in the content of the string. For Example say we have string 'ABC XYZ', I've to convert it into something like this 'ABC_XYZ'. Some strings do not have space in between and I should not add underscore in such cases. I'm working with MSSQL Server 2008 version.
I'm looking for a string function (or any other quick way) for adding a leading zero to make a string two characters long. For example, if the input is '12' the result will be '12' but if the input is '1', the result will be '01'.
It's actually about making a month number two characters long but I've understood there is no way to make Datediff() to fix it for me.
I thought there might be a string function for this, like there is in many programming languages, but I can't find anything in BOL. I want to keep it simple, because it will be used in a Select and a Group By for aggregating data based on time intervals (year + month).
And, I assume I can't make Datepart() to return year + month directly, only one of the parts at a time.
Hello, I would like to know if it is possible to add comments from different columns from table in sql into a single column. Please, see the attached table. I want all comments to be in one column. The outcome should be: CONFIRMED NEED TO CONFIRM SIGNER AT <CR><LF> received e-mail:<CR><LF> Called Imran again today to see if .
Ideally, I would like to receive all comments in a single string of characters, perhaps separating the comments by <CR><LF>
Here is the table. Sorry my table doesnt look good, but it has the following fields: DATE, LEXNEX_DECISION, COMMENT
DATE LEXNEX_DECISIONCOMMENT 4/3/2007COMPLETECONFIRMED NEED TO CONFIRM SIGNER AT 4/3/2007COMPLETEreceived e-mail: 4/3/2007COMPLETECalled Imran again today to see if .
I am using C# in  Visual Studio 2008 and remote database as sql server 2008 R2. I want to read remote database table's field value and i have to move that read value to string variable. how to do it.Â
And my code is :
string sql = "Select fldinput from tmessage_temp where fldTo=IDENT_CURRENT('tmessage_temp')"; SqlCommand exesql = new SqlCommand(sql, cn); exesql.CommandType = CommandType.Text; SqlDataReader rd1 = default(SqlDataReader); rd1 = exesql.ExecuteReader();
add a number to the end of an ID to create a series.For example, I have an EventID that may have many sub events. If the EventID is 31206, and I want to have subEvents, I would like have the following sequence. In this case, lets say I have 4 sub Events so I want to check the EventID and then produce:
312061 312062 312063 312064
How can I check what the EventID is, then concatenate a sequence number by the EventID?
I have a scenario where in I need to use a comma delimited string as input. And search the tables with each and every string in the comma delimited string.
We have some URLs within a bulk block of text some of which are very long. I need to identify rows where such urls exceed say 100 characters in length in amongst other text.So the rule would be return a record if within the string there is a string (without spaces) longer than 100 characters.
I have following query which return me SP/Views and Functions script using:
select DEFINITION FROM .SYS.SQL_MODULESNow, the result looks like Create proc create procedure create proc create view create function
I need its result as:
Alter Procedure Alter Procedure Alter Procedure Alter View Alter Function
I used following
select replace(replace(replace(DEFINITION,'CREATE PROCEDURE','Alter Procedure'), 'create proc','Alter Procedure'),'create view','Alter View') FROM .SYS.SQL_MODULESto but it is checking fixed space like create<space>proc, how can i check if there are two or more spaces in between create view or create proc or create function, it should replace as i want?
Hello to all, I have a problem with ms sql query. I hope that somebody can help me. i have a table "Relationships". There are two Fields (IDMember und RelationshipIDs) in this table. IDMember is the Owner ID (type: integer) und RelationshipIDs saves all partners of this Owner ( type: varchar(1000)). Example Datas for Table Relationships: IDMember Relationships . 3387 (2345, 2388,4567,....) 4567 (8990, 7865, 3387...) i wirte a query to check if there is Relationship between two members. Query: Declare @IDM int; Declare @IDO int; Set @IDM = 3387, @IDO = 4567; select * from Relationship where (IDMember = @IDM) and ( cast(@ID0 as char(100)) in (select Relationship .[RelationshipIDs] from Relationship where IDMember = @IDM))
But I get nothing by this query. Can Someone tell me where is the problem? Thanks
My requirement is that if the string in the column has any of the characters from 'ACDIPFJZ' , those characters have to be retained and the rest of the characters have to be removed.
I have a text field which has entries of variable length of the form:
"house:app.apx&resultid=1234,clientip" or "tost:app.apx&resultid=123,clientip" or "airplane:app.apx&resultid=123489,clientip"
I'm trying to pick out the numbers between resultid='...',clientip no matter what the rest of the string looks like. So in this example it would be the numbers:
1234 123 12389
the part of the string of the form resultid='...',clientip always stays the same except the length of the number can vary.
I'm a non-programmer and an SQL newbie. I'm trying to create a printer usage report using LogParser and SQL database. I managed to export data from the print server's event log into a table in an SQL2005 database.
There are 3 main columns in the table (PrintJob) - Server (the print server name), TimeWritten (timestamp of each print job), String (eventlog message containing all the info I need). My problem is I need to split the String column which is a varchar(255) delimited by | (pipe). Example:
2|Microsoft Word - ราย�ารรับ.doc|Sukanlaya|HMb1_SD_LJ2420|IP_192.10.1.53|82720|1
The first value is the job number, which I don't need. The second value is the printed document name. The third value is the owner of the printed document. The fourth value is the printer name. The fifth value is the printer port, which I don't need. The sixth value is the size in bytes of the printed document, which I don't need. The seventh value is the number of page(s) printed.
How I can copy data in this table (PrintJob) into another table (PrinterUsage) and split the String column into 4 columns (Document, Owner, Printer, Pages) along with the Server and TimeWritten columns in the destination table?
In Excel, I would use combination of FIND(text_to_be_found, within_text, start_num) and MID(text, start_num, num_char). But CHARINDEX() in T-SQL only starts from the beginning of the string, right? I've been looking at some of the user-defind-function's and I can't find anything like Excel's FIND().
Or if anyone can think of a better "native" way to do this in T-SQL, I've be very grateful for the help or suggestion.
I have a nasty situation in SQL Server 7.0. I have a table, in whichone column contains a string-delimited list of IDs pointing to anothertable, called "Ratings" (Ratings is small, containing less than tenvalues, but is subject to change.) For example:[ratingID/descr]1/Bronze2/Silver3/Gold4/PlatinumWhen I record rows in my table, they look something like this:[uniqueid/ratingIDs/etc...]1/2, 4/...2/null/...3/1, 2, 3/...My dilemma is that I can't efficiently read rows in my table, match thestring of ratingIDs with the values in the Ratings table, and returnthat in a reasonable fashion to my jsp. My current stored proceduredoes the following:1) Query my table with the specified criteria, returning ratingIDs as acolumn2) Split the tokens in ratingIDs into a table3) Join this small table with the Ratings table4) Use a CURSOR to iterate through the rows and append it to a string5) Return the string.My query then returns...1/"Silver, Platinum"2/""3/"Bronze, Silver, Gold"And is easy to output.This is super SLOW! Queries on ~100 rows that took <1 sec now take 12secs. Should I:a) Create a junction table to store the IDs initially (I didn't thinkthis would be necessary because the Ratings table has so few values)b) Create a stored procedure that does a "SELECT * FROM Ratings," putthe ratings in a hashtable/map, and match the values up in Java, sinceJava is better for string manipulation?c) Search for alternate SQL syntax, although I don't believe there isanything useful for this problem pre-SQL Server 2005.Thanks!Adam
Link ( GroupID int , MemberID int ) Member ( MemberID int , MemberName varchar(50), GroupID varchar(255) )
The Link table contains the records showing which Member is in which Group. One particular Member can be in multiple Groups and also a particular Group may have multiple Members.
The Member table contains the Member's ID, Member's Name, and a Group ID field (that will contains comma-separated Groups ID, showing in which Groups the particular Member is in).
We have the Link table ready, and the Member table' with first two fields is also ready. What we have to do now is to fill the GroupID field of the Member table, from the Link Table.
For instance,
Read all the GroupID field from the Link table against a MemberID, make a comma-separated string of the GroupID, then update the GroupID field of the corresponding Member in the Member table.
Please help me with a sql query or procedures that will do this job. I am using SQL SERVER 2000.
Hi,We have stored proc name proc_test(str nvarchar(30)). So far this prochas been invoked from a .NET application assuming that only Englishcharacter strings will be passed to it. The calls are likeproc_test('XYZ')We now have a requirement for passing Chinese strings as well. Ratherthan changing the calls throughout the application, we would like tohandle it in the stored procedure so that it treats the string as aunicode string. Can we apply some function to the parameter to convertit to unicode so that we don't have to call with an N prefixed to thestring?Thanks,Yash
When I enter over 4000 chars in any ntext field in my SQL Server 2005 database (directly in the database and through the application) I get an error saying that the data could not be updated because string or binary data would be truncated.Has anyone ever seen this? I cannot figure out what is causing it, ntext should be able to hold a lot more data that this...
"pRecordSet" is an ADO recordset. The database column "MyColumn" is of type "decimal(19,10)".
The most important question for me is, if the regional settings of the database server or the regional settings of the client PC are considered during the conversion from the string to the decimal value. For example in standard French regional settings the "." would not be recognized as decimal separator.
I am also wondering if the language of the database instance, in which this data is saved, is considered during this conversion or any other settings of this database instance.
So my general question is: Does anybody know exactly what rules apply during the above mentioned conversion?
i want to import data from an excel sheet into a database. While reading from the excel sheet OleDb automatically guesses the Datatype of each column. My Problem is the first A Column which contains ~240 Lines. 210 Lines are Numbers, the latter 30 do contain strings. When i use this code:
Code BlockDim sConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & conf_path_current & file_to_import & ";Extended Properties=""Excel 8.0;HDR=NO""" Dim oConn As New OleDb.OleDbConnection(sConn) Dim cmd1 As New System.Data.OleDb.OleDbCommand("Select * From [Table$]", oConn) Dim rdr As OleDb.OleDbDataReader = cmd1.ExecuteReader Do While rdr.Read() Console.WriteLine(rdr.Item(0)) 'or rdr(0).ToString Next
it will continue to read the stuff till the String-Lines are coming. when using Item(0), it just crashes for trying to convert a DBNull to a String, when using rdr(0).ToString() it just gives me no value.
So my question is how to tell OleDB that i want that column to be completly read as String/Varchar?
Thanks for Reading
- Pierre from Berlin
[seems i got redirected into the wrong forum, please move into the correct one]
A data reader is using a connection manager to connect to an ODBC System DSN . A query in the SqlCommand property is provided. Data is being truncated in the only string column . The data type in data reader output-->external columns shows as Unicode string [DT_WSTR] Length 7.
The truncated output in a text file is the first 3 characters from left to right . Changing the column order has no effect.
A linked server was created in SQL Server Management Studio to test the ODBC System DSN using the following:
Data returned using "OPENQUERY" does not truncate the string column indicating that the ODBC Driver returns data as expected with sql 2005, but not with the Data Reader.
I'm trying to import a student id in one database (nvarchar(7)) to int in another database. No sure whether I need ato use cast or convert. Anyone have some examples?
I am studying indexes and keys. I have a table that has a fixed width of data to be loaded in the first column which is parsed in a view based on data types within the fixed width specifications.
Example column A: (name phone house cost of house,zipcodecountystatecountry) -a view will later split this large varchar string based column b: is the source filename of the data load (varchar 256) ....
a. would there be a benefit of adding a clustered or nonclustered index (if so which/point in direction on why)
b. is there benefit of making one of these two columns a primary key (millions of records) or for adding a 3rd new column as a pk?
c. view: this parses the data in column a so it ends up looking more like "name phone house cost of house zipcode county state country" each having their own column.
-any pros/cons of adding indexes (if so which) to the view instead of the tables or both for once the data is parsed?