Relations Error -

Dim verilaDataAdapter1 As SqlCeDataAdapter
Dim verilaDataAdapter2 As SqlCeDataAdapter
Dim iliski As DataRelation
Dim Sorgu1 As String
Dim Sorgu2 As String

Dim A1Dataset As DataSet
Dim parametre1 As SqlCeParameter
Dim parametre2 As SqlCeParameter
Dim ssceconn As New SqlCeConnection("Data Source =\Program Files\AtisoftDA\atisoft.sdf")
ssceconn.Open()
Dim sqlInsertRow As SqlCeCommand = ssceconn.CreateCommand()

Sorgu1 = "SELECT * FROM StokKartTbl WHERE StokGrupKodu like @.StokGrupKodu Order By Stokismi ASC;"
Sorgu2 = "SELECT * FROM SarzKartTbl WHERE SarzID like @.Sarzid"

A1Dataset = New DataSet

verilaDataAdapter1 = New SqlCeDataAdapter(Sorgu1, ssceconn) 'StokkartTbl
parametre1 = New SqlCeParameter("@.StokGrupKodu", SqlDbType.NText, 25)
parametre1.Value = Trim(Mid(CBStGrupAciklama.Text, 1, 6)) + "%"
verilaDataAdapter1.SelectCommand.Parameters.Add(parametre1)
verilaDataAdapter1.Fill(A1Dataset, "StokKartTbl")

verilaDataAdapter2 = New SqlCeDataAdapter(Sorgu2, ssceconn) 'SarzKartTbl
parametre2 = New SqlCeParameter("@.Sarzid", SqlDbType.NText, 50)
parametre2.Value = Trim(Mid(CBSarzID.Text, 1, 25)) + "%"
verilaDataAdapter2.SelectCommand.Parameters.Add(parametre2)
verilaDataAdapter2.Fill(A1Dataset, "SarzKartTbl")

Dim p As DataColumn
Dim c As DataColumn
p = A1Dataset.Tables("StokKartTbl").Columns("StokID")
c = A1Dataset.Tables("SarzKartTbl").Columns("StokID")
iliski = New DataRelation("Elemanlar", p, c)
A1Dataset.Relations.Add(iliski)

DataGridStokKartTbl.DataSource = A1Dataset.Tables("StokKartTbl")

'Error

System.ArgumentException was unhandled
Message="Hata iletisi i?eren iste?e ba?l? bir kaynak derlemesi bulunamad???ndan bir hata iletisi g?rüntülenemiyor"
StackTrace:
System.Data.ConstraintCollection.AddForeignKeyConstraint()
System.Data.ConstraintCollection.Add()
System.Data.ConstraintCollection.Add()
at DataSetRelationCollection.AddCore()
System.Data.DataRelationCollection.Add()
AtisoftDA.siparis.Button2_Click()
System.Windows.Forms.Control.OnClick()
System.Windows.Forms.Button.OnClick()
System.Windows.Forms.ButtonBase.WnProc()
System.Windows.Forms.Control._InternalWnProc()
Microsoft.AGL.Forms.EVL.EnterMainLoop()
System.Windows.Forms.Application.Run()
AtisoftDA.siparis.Main()

Hi AtiSoft,

Looks like the community did not really understand your problem or the post semantics made them to show less interest in this thread. If you are still facing the problem, I would request you to post a new query with a detailed problem description than just the program dump!

Thanks,

SQL Server Mobile/Everywhere Forum Moderator

regular expressions in transact sql

hi guys,

i need some help tp write code in order to search the string ( regular expressions) in t- sql.

e.g.

when a user enters [A-Z] it means any alphabet from 'A' to 'Z'...

similarly [0-9] means any digit.

the problem is: when user enters [0-6] and the string received contains digit 5 it should return true but if it contains 7 it should return false.

so how do i read the [A-Z] as a range of characters in t-sql?

hi,

In sql server it is not exactly regular expression, it is called wild card pattern. in other words it is simplified reqular expression,

as of now SQL Server Like operator only work with following operators

% - Zero or any number of chars

_ - Single Char

[] - Single Char in given range

- Single Char not in given range

if you want to utilize the exact regular expression on your query then the best solution will be CLR Functions.(SQL Server 2005).

For fixed validation (only numbers & only alphabets) i achived the following function,

Create Function dbo.IsMatching(@.Value as varchar(1000), @.Pattern as varchar(100))
returns bit as
Begin
Declare @.Len as int;
Declare @.SearchPattern as varchar(8000);
Declare @.Result as Int;

Select @.Len = Len(@.Value);

While @.Len>0
Begin
Select @.SearchPattern = Isnull(@.SearchPattern,'') + @.Pattern;
Select @.Len = @.Len -1;
End
Select @.Result = Case When @.Value Like @.SearchPattern Then 1 Else 0 End;
Return @.Result;
End

Go

select dbo.IsMatching('SQLServer','[A-Z]') as Result

Result : 1

select dbo.IsMatching('SQL Server','[A-Z]') as Result

Result : 0 --Space on String

select dbo.IsMatching('SQL Server','[A-Z ]') as Result

Result : 1 --Space added on Pattern

select dbo.IsMatching('12453','[1-5]') as Result

Result : 1

select dbo.IsMatching('12463','[1-5]') as Result

Result : 0

|||

thanks mani,

got the [A-Z] and [^a-z] concept.

my other requirements are to match zero or more characters and to match one or more characters.

e.g. T*he should match he, the, tthe, ttttthe.. etc.

and t+ho should match tho, thho, thhhhhhho, thhhhhhhhhhhhhho.. etc.

the above operators i have used in VC++,

do they work in t-sql too?

|||If you need regular expression and your platform is sqlserver 2005 you can use a CLR strored procedure. If you need help on this post a question on the .net framework inside sql server forum
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=86&SiteID=1|||

Yes.. if you want to find the repeated chars you can use the following condtions....

columnname not like '%aaa%'
and columnname not like '%bbb%'
and columnname not like '%ccc%'
and columnname not like '%ddd%'
and columnname not like '%eee%'
and columnname not like '%fff%'
and columnname not like '%ggg%'
and columnname not like '%hhh%'
and columnname not like '%iiii%'
and columnname not like '%jjj%'
and columnname not like '%kkk%'
and columnname not like '%lll%'
and columnname not like '%mmm%'
and columnname not like '%nnn%'
and columnname not like '%ooo%'
and columnname not like '%ppp%'
and columnname not like '%qqq%'
and columnname not like '%rrr%'
and columnname not like '%sss%'
and columnname not like '%ttt%'
and columnname not like '%uuu%'
and columnname not like '%vvv%'
and columnname not like '%www%'
and columnname not like '%xxx%'
and columnname not like '%yyy%'
and columnname not like '%zzz%'

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=996813&SiteID=1

If you want to utilize the exact regular expression as i said earlier you can go for CLR Functions..

Registry configuration problems ?

Hi,

Quite new to SSIS am currently trying to set up a registry configuration which will hold a connection string which will enable the main SQL configurations for a package to load. I wish to put the registry entry under the HKEY_LOCAL_MACHINE directory or more precisely in a key such as below:

HKEY_LOCAL_MACHINE\SOFTWARE\My Company\My Project\ETLConfigDbString

Have set this key up with a default value of the connection string and an associated registry configuration with the above as the value in the regsitry entry box. This configuration is listed as the first configurartion for the package to ensure it gets applied before the SQL configuration loads.

However on load or execute I get a warning of the type

A configuration entry specifies a registry key that is not available. Check the registry to ensure that the key is there.

Are there any known problems with regsitry configurations or am I just missing something obvious here ?

Thanks

Right may have answered my own question. Digging around on tghe Net a bit more I think it's that you can only have a registry entry which is under HKEY_CURRENT_USER (or one of its subkeys). It also must have a value called Value which must be a DWORD or string.

Can someone confirm this is correct ? Was hoping to use the HKEY_LOCAL_MACHINE key as that's what our company already uses for other software values but obviously if SSIS is restricted to HKEY_CURRENT_USER then that's not going to work.

Thanks

|||From the example given in Books Online, it does appear that keys have to be located under HKEY_CURRENT_USER. However, the text isn't as clear as it could be, so I'm not sure this is a definitive answer.

|||

Thanks John. I couldn't get the HKEY_LOCAL_MACHINE key to work so I've switched to using a system enviroment variable instead now. Didn't want to use HKEY_CURRENT_USER as the SQL configuration database is always going to be the same regardless of who the user running the package is so it didn't seem a good solution to use HKEY_CURRENT_USER.

To be honest (IMO) it's not really paticularly good from a deployment/production point of view either. I was intending to supply our DBA with a .reg key they could run to set up the registry entry put obviously I can't do that if I have to use HKEY_CURRENT_USER as I don't know what the SQL Agent user GUID is going to be in the registry.

So all in all a system enviroment variable seemed a better solution.

|||

I prefer environment variables myself, or a single XML configuration file.

Registration form problem. String or binary data would be truncated

I have created a registration form. It works fine IF ALL fields are filled. However, all fields are not required. When I test the registration page and leave a field blank, I receive the following message:

Exception Details: System.Data.SqlClient.SqlException: String or binary data would be truncated. The statement has been terminated.

Stack Trace:

[SqlException: String or binary data would be truncated.
The statement has been terminated.]

Even if I leave a validated field blank, I receive the same message instead of the required field validation control error message.

Here is the code for the registration page. I use web matrix to create the code as directed in the tutorial.

Can anyone help a newbie?

Function AddMember( _
 ByVal firstName As String, _
 ByVal lastName As String, _
 ByVal streetAddress1 As String, _
 ByVal streetAddress2 As String, _
 ByVal memCity As String, _
 ByVal state As String, _
 ByVal zipCode As String, _
 ByVal primAreaCode As String, _
 ByVal primPhone As String, _
 ByVal primExt As String, _
 ByVal secAreaCode As String, _
 ByVal secPhone As String, _
 ByVal secExt As String, _
 ByVal memEmail As String, _
 ByVal memUserID As String, _
 ByVal memPassword As String, _
 ByVal secretQuestion As String, _
 ByVal secretAnswer As String, _
 ByVal memBirthMonth As String, _
 ByVal memBirthDay As String, _
 ByVal memBirthYear As String) As Integer
 Dim connectionString As String = "server='(local)'; trusted_connection=true; database='Members'"
 Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
 Dim queryString As String = "INSERT INTO [Members] ([FirstName], [LastName], [StreetAddress1], [StreetAddress2"& _
"], [MemCity], [State], [ZipCode], [PrimAreaCode], [PrimPhone], [PrimExt], [SecAr"& _
"eaCode], [SecPhone], [SecExt], [MemEmail], [MemUserID], [MemPassword], [SecretQu"& _
"estion], [SecretAnswer], [MemBirthMonth], [MemBirthDay], [MemBirthYear]) VALUES "& _
"(@.FirstName, @.LastName, @.StreetAddress1, @.StreetAddress2, @.MemCity, @.State, @.Zip"& _
"Code, @.PrimAreaCode, @.PrimPhone, @.PrimExt, @.SecAreaCode, @.SecPhone, @.SecExt, @.Me"& _
"mEmail, @.MemUserID, @.MemPassword, @.SecretQuestion, @.SecretAnswer, @.MemBirthMonth"& _
", @.MemBirthDay, @.MemBirthYear)"
 Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
 dbCommand.CommandText = queryString
 dbCommand.Connection = dbConnection
 Dim dbParam_firstName As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_firstName.ParameterName = "@.FirstName"
 dbParam_firstName.Value = firstName
 dbParam_firstName.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_firstName)
 Dim dbParam_lastName As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_lastName.ParameterName = "@.LastName"
 dbParam_lastName.Value = lastName
 dbParam_lastName.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_lastName)
 Dim dbParam_streetAddress1 As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_streetAddress1.ParameterName = "@.StreetAddress1"
 dbParam_streetAddress1.Value = streetAddress1
 dbParam_streetAddress1.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_streetAddress1)
 Dim dbParam_streetAddress2 As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_streetAddress2.ParameterName = "@.StreetAddress2"
 dbParam_streetAddress2.Value = streetAddress2
 dbParam_streetAddress2.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_streetAddress2)
 Dim dbParam_memCity As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_memCity.ParameterName = "@.MemCity"
 dbParam_memCity.Value = memCity
 dbParam_memCity.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_memCity)
 Dim dbParam_state As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_state.ParameterName = "@.State"
 dbParam_state.Value = state
 dbParam_state.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_state)
 Dim dbParam_zipCode As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_zipCode.ParameterName = "@.ZipCode"
 dbParam_zipCode.Value = zipCode
 dbParam_zipCode.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_zipCode)
 Dim dbParam_primAreaCode As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_primAreaCode.ParameterName = "@.PrimAreaCode"
 dbParam_primAreaCode.Value = primAreaCode
 dbParam_primAreaCode.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_primAreaCode)
 Dim dbParam_primPhone As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_primPhone.ParameterName = "@.PrimPhone"
 dbParam_primPhone.Value = primPhone
 dbParam_primPhone.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_primPhone)
 Dim dbParam_primExt As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_primExt.ParameterName = "@.PrimExt"
 dbParam_primExt.Value = primExt
 dbParam_primExt.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_primExt)
 Dim dbParam_secAreaCode As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_secAreaCode.ParameterName = "@.SecAreaCode"
 dbParam_secAreaCode.Value = secAreaCode
 dbParam_secAreaCode.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_secAreaCode)
 Dim dbParam_secPhone As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_secPhone.ParameterName = "@.SecPhone"
 dbParam_secPhone.Value = secPhone
 dbParam_secPhone.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_secPhone)
 Dim dbParam_secExt As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_secExt.ParameterName = "@.SecExt"
 dbParam_secExt.Value = secExt
 dbParam_secExt.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_secExt)
 Dim dbParam_memEmail As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_memEmail.ParameterName = "@.MemEmail"
 dbParam_memEmail.Value = memEmail
 dbParam_memEmail.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_memEmail)
 Dim dbParam_memUserID As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_memUserID.ParameterName = "@.MemUserID"
 dbParam_memUserID.Value = memUserID
 dbParam_memUserID.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_memUserID)
 Dim dbParam_memPassword As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_memPassword.ParameterName = "@.MemPassword"
 dbParam_memPassword.Value = memPassword
 dbParam_memPassword.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_memPassword)
 Dim dbParam_secretQuestion As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_secretQuestion.ParameterName = "@.SecretQuestion"
 dbParam_secretQuestion.Value = secretQuestion
 dbParam_secretQuestion.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_secretQuestion)
 Dim dbParam_secretAnswer As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_secretAnswer.ParameterName = "@.SecretAnswer"
 dbParam_secretAnswer.Value = secretAnswer
 dbParam_secretAnswer.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_secretAnswer)
 Dim dbParam_memBirthMonth As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_memBirthMonth.ParameterName = "@.MemBirthMonth"
 dbParam_memBirthMonth.Value = memBirthMonth
 dbParam_memBirthMonth.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_memBirthMonth)
 Dim dbParam_memBirthDay As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_memBirthDay.ParameterName = "@.MemBirthDay"
 dbParam_memBirthDay.Value = memBirthDay
 dbParam_memBirthDay.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_memBirthDay)
 Dim dbParam_memBirthYear As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_memBirthYear.ParameterName = "@.MemBirthYear"
 dbParam_memBirthYear.Value = memBirthYear
 dbParam_memBirthYear.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_memBirthYear)
 Dim rowsAffected As Integer = 0
 dbConnection.Open
 Try
 rowsAffected = dbCommand.ExecuteNonQuery
 Finally
 dbConnection.Close
 End Try
 Return rowsAffected
 End Function

Do u have all the fields in the table defined as "not null"?|||no. some fields allow nulls.