I am using Visual Studio 2005. I am trying to create a ASP.net website and use a web form to get the results of an enquiry. It is almost the same as the presentation video on this website called "How to Create Data Driven Website", but I have added a new twist to it. I want to use the "LIKE" instead of "=" in my SQL statement and I want to use wild cards in the parameter value that I enter.
I am new to SQL Server, DTS etc... But here is the question I have:
I have some files in a folder which have a the yr, month, date appended at the end, for e.g. testfile2005_11_28.log In a folder I have to look for the log files which are named testfileYYYY_MM_DD.log.
Dim item For Each item In logFolder.Files If (UCASE(item.Name) like ("TESTFILE" +"[0-9][0-9][0-9][0- 9]"+"_"+"[0-12][0-12]"+"_"+"[0-3][0-9]" + ".log") )Then msgbox item.Name End For
I am getting compilation error: "Sub or Function not found" in the If statement. Can anyone please help me?
Hi, I got a problem. I installed Microsoft SQL Server Management Studio Express 2005 version. And I created a Compact database. I created an connection in SSMSE to connect the database and opened a query form. then, i run the following sql:
Select * from Table1
It returned 3 records to me. After that, I used program to insert record into this table. Then i ran this sql again, it still show me 3 records. I closed the query form, and re-created a new query form, then run the sql, it returned 4 records to me.
Why? It's very strange and difficult to operate, right? Is there anyone know how to make the SSMSE to return whole records without any close query form and re-create query form operation?
table Orders ord_id int ord_CudtomerID int, ord_CardNo char(16)
Example values in table CreditCardRange 1 , 5000 0000 2000 1234 , 5000 0030 3000 1356
Example values in table Orders 1, 1, 5000 0010 2000 2333
Does sql provide any magic finction to help in following select
select T1.* from Orders T1 join CreditCardRange T2 on T2.ccr_CudtomerID = t1.ord_CudtomerID and SQLMagicFunction(T1.ord_CardNo) between SQLMagicFunction(T2.ccr_CardRangeFrom ) and SQLMagicFunction(T2.ccr_CardRangeTo )
We need to store credit cards for a limited amount of time because we charge for orders as they ship and occasionally the authorization expires before the item can ship.
I know I can use AES_256 to encrypt our credit cards but I am not sure if all I need to do is follow the directions in the "How-to" for encrypting data.
I created the master key, backed it up off the server the certificate and the symmetric key and I can encrypt and decrypt data.
Is this all I need to do or am I missing something?
Table name 1 : Income Income RentMonth 1500 Jan 1500 Feb 1500 Apr
Table name 2 : Expense Expense ExpMonth 200 Jan 300 Mar 400 Apr
The result table becomes (Profit=Income-Expense) Profit Month 1300 Jan 1500 Feb -300 Mar 1100 Apr
But I form the query by join the both Income and Expense tables to subtract the Income and Expense month wise.
But one moth is in one table the same month is not in another table.
For Example Feb month is in Income table, but not in Expense table.And Mar month is in Expense table and not in Income table. So how will I form the query to achieve my result table as i indicated above. Kindly help me.
What I want i, if there is any value in any fields, other null values in the field should be replaced by 0, if all the rows of the filed are null, simply leave as it is....
Like the select statement of this SIM_TempCustomer should give a result like
I have an ASP form that takes the information that is entered on the form and inserts it into a Microsoft Access database. It works great. In addition to the fields from the form, I also want to add the current date into the InitDate field. How would I modify the SQL query below to insert the current date into the COS database? conn.execute SqlQry Sql = "INSERT INTO COS ([Name of School], [Director of COS], [Address], [City], [State], [zip], [PhoneNumber], " Sql = Sql & "[general_notes], [type], [DEPT], ) " Sql = Sql & "VALUES ('" & m_CompanySchoolName & "', '" & m_FullName & "', '" & m_StreetAddress & "', '" & m_City Sql = Sql & "', '" & m_State & "', '" & m_Zip & "', '" & m_TelephoneNumber & "', '" & m_Message & "', 'COSMETOLOGY', '" Sql = Sql & m_Department & "', '" & m_EmailAddress & "')" response.write Sql response.end conn.execute Sql
Greetings! Is this a good SQL query to access two columns in my database and present this data in my web app? string DataBaseCommand = "Select UserName, [Password] from PatientDemographics Where UserName = '" + txtUserName.Text + "' && [Password] = '" + txtPassword.Text + "'"; Thank you,
I am trying to run a UNION ALL query in SQL SERVER 2014 on multiple large CSV files - the result of which i want to get into a table in SQL Server. below is the query which works in MSAccess but not on SQL Server 2014:
SELECT * INTO tbl_ALLCOMBINED FROM OPENROWSET ( 'Microsoft.JET.OLEDB.4.0' , 'Text;Database=D:DownloadsCSV;HDR=YES', 'SELECT t.*, (substring(t.[week],3,4))*1 as iYEAR, ''SPAIN'' as [sCOUNTRY], ''EURO'' as [sCHAR],
[Code] ....
What i need is:
1] to create the resultant tbl_ALLCOMBINED table
2] transform this table using PIVOT command with following transformation as shown below:
PAGEFIELD: set on Level = 'Item' COLUMNFIELD: Sale_Week (showing 1 to 52 numbers for columns) ROWFIELD: sCOUNTRY, sCHAR, CATEGORY, MANUFACTURER, BRAND, DESCRIPTION, EAN (in this order) DATAFIELD: 'Sale Value with Innovation'
3] Can the transformed form show columnfields >255 columns i.e. if i want to show all KPI values in datafield?
P.S: the CSV's contain the same number of columns and datatype but the columns are >100, so i dont think it will be feasible to use a stored proc to create a table specifying that number of columns.
Hello, My company Intranet has a form that agents can use to post their comments about the company to upper management, but our customer service department would like to modify the form so that the agent has to pick from a comment type. The dropdown options on the form will be as follows: ComplimentsComplaintsGeneral CommentsSuggestions Each dropdown option has a designated table in a SQL DB.Using postback on the same page, I need to change which fields of the form are visible based upon which dropdown selection the user chooses, and I need the fields to then be inserted into the table that corresponds with the dropdown selection item. For example: If the Compliments dropdown selection is picked, I need a text box to show for the user's location, the name of the customer, account number, and the message box. Once the submit button is clicked, the characters in these boxes need to be inserted into the Compliments table using its data adapter. However, if the user selects Suggestions, the name of the customer and the account number should not be visible, since these fields do not exist and when the submit button is pressed, the Suggestions table should be updated. If you need more information, I will provide whatever is needed. As always, thanks for everyone's assistance. Chris
I'm trying to look up customer records by e-mail domain by using a text box on a Web form. So if I want to look for all my customers that have an aol e-mail domain, I would type aol.com in the text box and the sub routine would know to count 7 characters from the right and through those characters into maybe a parameter query. I'm having problems passing this in. I can count the characters properly by using: dim strText = MyTextBox.Textdim intLength = strText.Length but having problems starting here...... MyCommand.SelectCommand.Parameters("@email").Value = MyTextBox.Text .............. but how would I ultimately feed this into my sql satement? Select * from Customers Where email = right(@email,intLength) Help appreciated. Frank
Frustration has gotten the best of me on this one. Can anyone help? I need to pass the current value in an Access Data Project (back-end is MS SQL) text field to the where condition in an SQL using VBA. This is what I have, but does not work.
Private Sub Command44_Click() Me.SS.SetFocus Dim strSQL As String Dim strSSecurity As String strSSecurity = Me.SS strSQL = "Update Employees Set employees.PositionID = '',employees.jobcode = '' Where employees.ss = strSSecurity" DoCmd.RunSQL strSQL End Sub
It is supposed to take the current Social Security number from the form and match it against the employees table. Once it finds the matching record it should update the PositionID and JobCode fields to '' (empty string) But it doesn't Anyone with any ideas? Thanks Dan
using a query or vba ,transfer data from one form to another, table1 customers-form1 customer address, table2 orders-form2 order address, how do i transfer data from customer address to order address yours M [/center]
Hi, I am a newbie, this is my first post (please go easy).
Iam at the moment trying to set up a query for someone looking for a property on an estate agents website.
From a drop down menu, the user can: select an area (where they may like to live) from a list of areas. select an amount of bedrooms from a list of bedrooms select a minimum price from a list of prices select a maximum price from a list of prices.
The query I worked out for this is as follows: $data = mysql_query("SELECT * FROM property WHERE area like '$area' and bedrooms like '$bedrooms' AND price BETWEEN '$min_price' AND '$max_price'") or die(mysql_error());
This seems to work fine and shows all the properties that meet the criteria onto my webpage.
However, I then thought, someone may not care which area they live in and want to see all properties in all the areas, so I decided to add the option 'All areas' to my 'areas' list, I then did the same for the other lists, eg 'all bedrooms' option to my bedrooms list and so on.
I am now trying to write a query that incorporates where the 'all..' option is selected and have become very stuck! Can someone set me off in the right direction for this. I hope that makes sense?!?!
Question related to Visual Basic Video Lesson 09 (Databinding.wmv) by Bob Tabor.
Made a table and a UI form according to this lesson (table colums: CustomerID, FirstName, LastName).
Is it possible to use the same or an identical looking query form to find "Bob" by typing Bob in the Fist Name textbox or "Tabor" by typing Tabor in the Last Name textbox as in FileMaker?
I'm trying to put together a script that would alter a table and add NOT FOR REPLICATION to the identity column on several hundred tables. Since I am unable to write a complex script to automate the entire process, I've decided to do the update one table at a time. Here is a script that I'm working on. I'm getting an error message when I try to run the script. Can someone please show me how to properly use variable with a wild card in a string ? or how to properly add this variables in this script?
DECLARE @Var1 AS CHAR(50), @tbn AS CHAR(50) SELECT @tbn = 'tablename' SELECT @Var1 = Name FROM SysIndexes WHERE Name LIKE 'pk%' AND Name LIKE '%' + @tbn + '%' EXECUTE('ALTER TABLE [@tbn] DROP CONSTRAINT ' + @Var1 ALTER TABLE + @tbn + DROP COLUMN mkey ALTER TABLE + @tbn + ADD viewkey INT IDENTITY (1,1) NOT FOR REPLICATION NOT NULL ALTER TABLE + @tbn + ADD CONSTRAINT PK_+ @tbn PRIMARY KEY (mkey) WITH FILLFACTOR=90 ON [PRIMARY]
I am developing one web site, in that I can upload files to sql server, it will save the uploaded file in binary type and we can download the files from there. Upto this everything is working perfectly. But now my problem is - i have to implement the wildcard character search in this site. The file name and extension will save on the database in different columns, and one more thing is file name is unique. I confused to implement wildcard search in this site, where i can implement this type of search in database or in my application with c#? So please help me to do this. I dont know that this is the right place to post this question or not. If it is not right place, please guide me to where I can post this. If it is right place, please help me to implement wild card search.
I am very new to sql and would like some help with a simple select statement. I am trying to pull birthdates using the following code. I can get the query to run but some of the dates are not between the parameters. What am i doing wrong?
SELECT Employees.EmployeeStatusID, People.FirstName, People.LastName, People.BirthDate FROM Employees FULL OUTER JOIN People ON Employees.SystemAssignedPersonID = People.SystemAssignedPersonID WHERE (People.BirthDate BETWEEN '10 - 01 - 1900' AND '12 - 15 - 2007') ORDER BY People.BirthDate
How do I write an sql statement to link tables in two databases and query them? For example: SELECT db1.table1.field1, db2.table1.field1 FROM Table1 INNER JOIN db1.table1.field1 ON db2.table1.field1 = db1.table1.field1
I need to write a sql that generate the hierarchy in an organization.Below an example
emplid empname supervisor_id superv_name 1 subu null null 2 vid 1 sub 3 ram 4 satis 4 satis 2 vid
i need an output to this query as below and also one important the supervisor ie supervisor_id and name is null is the top level,every employee also has to report to him and also to his all above supervisors.whoever joinng new to org the hierachy should be follwed
empid empname supervisor_id superv_name 3 ram 4 satis 4 satis 2 vid 2 vid 1 subu 4 satis 1 subu 3 ram 1 subu 3 ram 2 vid 5 kumar 1 subu 5 kumar 4 satis 5 kumar 2 vid 1 subu null null
I've written a Union query that joins 16 different tables. I need to somehow make this into a permanent view so that i can query directly from excel to extract specific data - i guess i can set up excel to pull straight from this view then ?
I'm used to running excel VB code to SQL via Access (linking access up) but want to cut out the middleman now.
Any ideas ?
An example of the Union Query i have built is below :
Hello everyone, trying to use stored procedure for my datagrid. in there i have the parameter that i would like to combine with wild character to retrieve some data..Does not work. Please help to come up with right syntax... here is the code: SELECT First_Name, Last_Name, Address, City, Customer_ID, Company_Name, State, ZIP, Phone_Number_1, Phone_Number_2, Email FROM v2_Customers WHERE (City = @search_text) OR (First_Name = @search_text) OR (Last_Name LIKE '%'+@search_text) OR (Address = @search_text)
In the below section of code I'm connecing to a SQL database and when I use the "=" in the search it works ok, but when I try to add the ability to use wildcards by changin "=" to "LIKE" its not working
Working code:
Function GetName(ByVal first_Name As String) As System.Data.DataSet Dim connectionString As String = "server='(local)'; trusted_connection=true; database='FamilyInfo'" Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "SELECT Names.* FROM Names WHERE (Names.First_Name = @First_Name)" Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand dbCommand.CommandText = queryString dbCommand.Connection = dbConnection
Dim dbParam_first_Name As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_first_Name.ParameterName = "@First_Name" dbParam_first_Name.Value = first_Name dbParam_first_Name.DbType = System.Data.DbType.StringFixedLength dbCommand.Parameters.Add(dbParam_first_Name)
Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter dataAdapter.SelectCommand = dbCommand Dim dataSet As System.Data.DataSet = New System.Data.DataSet dataAdapter.Fill(dataSet)
Return dataSet End Function
Code that I would like to work
Function GetName(ByVal first_Name As String) As System.Data.DataSet Dim connectionString As String = "server='(local)'; trusted_connection=true; database='FamilyInfo'" Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "SELECT Names.* FROM Names WHERE (Names.First_Name LIKE '%' + @First_Name '+')" Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand dbCommand.CommandText = queryString dbCommand.Connection = dbConnection
Dim dbParam_first_Name As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_first_Name.ParameterName = "@First_Name" dbParam_first_Name.Value = first_Name dbParam_first_Name.DbType = System.Data.DbType.StringFixedLength dbCommand.Parameters.Add(dbParam_first_Name)
Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter dataAdapter.SelectCommand = dbCommand Dim dataSet As System.Data.DataSet = New System.Data.DataSet dataAdapter.Fill(dataSet)
Hi All, I'm trying to use a very simple SQL String that check if a specific string exist in the DB. When I do the SQL and use a regular Connection I get the result. When I use a data set and on the table adapter I need to use the "LIKE %" method I get an error.
What should I write on the table adapter in order to make this search?
ItemID is a type string.
This is my SQL: SELECT itemID, itemName FROM Items WHERE (itemID LIKE @ItemIDTemp)
This sample give no result or
SELECT itemID, itemName FROM Items WHERE (itemID LIKE '%' + @ItemIDTemp + '%')
This sample return an error message "Data conversion failed. [OLEDB status value (if known)=2]"
Dear All, I have created a table in my SQL server database, the problem i am facing is my insert query fails if i leave any form field empty (leave it blank). On my back-end table, only one field is mandatory, and others have been set with the constraint "allow null". As per our business requirement, except one value is complusory while others are optional. If I enter all values in the form it works perfectly fine. Can you see in the code below - where am i possibly going wrong ? <script language="VB" runat="server" > Sub Page_Load(Src As Object, e As EventArgs) If Page.IsPostBack Then Dim ConLath As SqlConnection Dim comLath As SqlCommand Dim insertcmd conLath = New SqlConnection("Data Source=SQLas;Initial Catalog=settle;User ID=sa;Password=password") ConLath.Open() insertcmd = "Insert into His_set values (@t_d,@s_p,@p_s,@v_oq,@i_oq,@v_qn,@i_qn,@v_qw,@i_qw)" comLath = New SqlCommand(insertcmd, ConLath) comLath.Parameters.Add(New SqlParameter("@t_d", SqlDbType.DateTime, 12)) comLath.Parameters("@t_d").Value = trade_date.Text comLath.Parameters.Add(New SqlParameter("@s_p", SqlDbType.Decimal, 8)) comLath.Parameters("@s_p").Value = sett_price.Text comLath.Parameters.Add(New SqlParameter("@p_s", SqlDbType.Decimal, 8)) comLath.Parameters("@p_s").Value = post_close.Text comLath.Parameters.Add(New SqlParameter("@v_oq", SqlDbType.Int, 8)) comLath.Parameters("@v_oq").Value = vol_oq.Text comLath.Parameters.Add(New SqlParameter("@i_oq", SqlDbType.Int, 8)) comLath.Parameters("@i_oq").Value = oi_oq.Text comLath.Parameters.Add(New SqlParameter("@v_qn", SqlDbType.Int, 8)) comLath.Parameters("@v_qn").Value = vol_qn.Text comLath.Parameters.Add(New SqlParameter("@v_qw", SqlDbType.Int, 8)) comLath.Parameters("@v_qw").Value = vol_qw.Text comLath.Parameters.Add(New SqlParameter("@i_qn", SqlDbType.Int, 8)) comLath.Parameters("@i_qn").Value = oi_qn.Text comLath.Parameters.Add(New SqlParameter("@i_qw", SqlDbType.Int, 8)) comLath.Parameters("@i_qw").Value = oi_qw.Text Try comLath.ExecuteNonQuery() Catch ex As SqlException If ex.Number = 2627 Then Message.InnerHtml = "ERROR: A record already exists with " _ & "the same primary key" Else Message.InnerHtml = "ERROR: Could not add record, please " _ & "ensure the fields are correctly filled out" Message.Style("color") = "red" End If End Try comLath.Dispose() ConLath.Close() End If End Sub </script>
