Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 971
  • Last Modified:

SQL Server Connection String Help Visual Studio 2010

As I understand it you cannot use VS 2010 connection wizard with a sql 2000 db. I have seen where people have got this to work using code. I am trying to acomplish this by using the following code below.

I am a novice at programmng at best. I have tried this code but get an error. The error I get is on this line: Dim Global_strProdCN As String = Global_settings.ConnectionString
NullReferenceExceptionHandeled. I have not idea how to correct this given my "newbie" skills. The complete code is below.

Imports System.Data.SqlClient
Imports System.Configuration
Imports System
Imports System.IO
Imports System.Net.Mime.MediaTypeNames


Public Class Form1

    Dim Global_settings As ConnectionStringSettings
    Public Global_strProdCN As String
    Public Global_sqlProdCN As New SqlConnection
    Public Global_ERPsettings As ConnectionStringSettings
    Public Global_strERPCN As String
    Public Global_sqlERPCN As New SqlConnection
    Public Global_dr As SqlDataReader
    Public Global_sqlcmd As New SqlCommand
    Public Global_strsql As String
    Public Global_strProdCN2 As String
    Public Global_sqlProdCN2 As New SqlConnection

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Global_settings = System.Configuration.ConfigurationManager.ConnectionStrings("Data Source=SERVER04N;Initial Catalog=yamahalabeldb;Integrated Security=True")
        Dim Global_strProdCN As String = Global_settings.ConnectionString
        Dim irowcnt As Integer = -1

        Global_strsql += " SELECT Part_no, Deliverylocation, po_no, supplier_id, labelcode, qty, duedate, stocklocation, sirlocation "
        Global_strsql += " FROM yam_con_label "
        Global_strsql += " Order by part_no "
        Global_sqlProdCN = New SqlConnection(Global_strProdCN)
        Global_sqlProdCN.Open()
        Global_sqlcmd = New SqlCommand(Global_strsql, Global_sqlProdCN)
        Global_dr = Global_sqlcmd.ExecuteReader()
        If Global_dr.HasRows Then
            While Global_dr.Read
                irowcnt += 1
                Dim srow() As String = {Global_dr.Item("Part_no"), Global_dr.Item("Deliverylocation"), Global_dr.Item("po_no"), _
                                        Global_dr.Item("supplier_id"), Global_dr.Item("labelcode"), Global_dr.Item("qty"), _
                                        Global_dr.Item("duedate"), Global_dr.Item("stocklocation"), Global_dr.Item("sirlocation")}
                dgYamahadata.Rows.Add(srow)
            End While
        End If

    End Sub

0
tmaususer
Asked:
tmaususer
  • 9
  • 6
  • 5
1 Solution
 
anillucky31Commented:
first you need to add connection string into web.config file like this

<configuration>
  <connectionStrings>
    <add name="connectionstringname" connectionString="Data Source=SERVER04N;Initial Catalog=yamahalabeldb;Integrated Security=True;" />
  </connectionStrings>
</configuration>

this will work only if database and application server are on same machine. If you are using databse from other machine then you have to specify userid and password for ur server

<configuration>
  <connectionStrings>
    <add name="connectionstringname" connectionString="Data Source=SERVER04N;Initial Catalog=yamahalabeldb;user id =databseusername; pwd=databasepassword" />
  </connectionStrings>
</configuration>

then in you code

  Global_settings = System.Configuration.ConfigurationManager.ConnectionStrings("Data Source=SERVER04N;Initial Catalog=yamahalabeldb;Integrated Security=True")

instead use this

ConfigurationManager.ConnectionStrings["connectionstringname"].ConnectionString;


try this. it should work





0
 
MaxOvrdrv2Commented:
can you show me the connection string you declared in your settings?
0
 
MaxOvrdrv2Commented:
oh nm... i missed that connection directly in the code... if you're going to do that, just declare the connection like this:

dim oCon as New SQLConnection("Data Source=SERVER04N;Initial Catalog=yamahalabeldb;Integrated Security=True")
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
tmaususerAuthor Commented:
So here is where I placed your suggestion. I don't know if this is correct or not but now I get an error on the GlobalSqlprod.cn

Imports System.Data.SqlClient
Imports System.Configuration
Imports System
Imports System.IO
Imports System.Net.Mime.MediaTypeNames


Public Class Form1

    Dim Global_settings As ConnectionStringSettings
    Public Global_strProdCN As String
    Public Global_sqlProdCN As New SqlConnection
    Public Global_ERPsettings As ConnectionStringSettings
    Public Global_strERPCN As String
    Public Global_sqlERPCN As New SqlConnection
    Public Global_dr As SqlDataReader
    Public Global_sqlcmd As New SqlCommand
    Public Global_strsql As String
    Public Global_strProdCN2 As String
    Public Global_sqlProdCN2 As New SqlConnection

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Global_settings = System.Configuration.ConfigurationManager.ConnectionStrings("Data Source=SERVER04N;Initial Catalog=yamahalabeldb;Integrated Security=True")
        Dim oCon As New SqlConnection("Data Source=SERVER04N;Initial Catalog=yamahalabeldb;Integrated Security=True")
        'Dim Global_strProdCN As String = Global_settings.ConnectionString
        Dim irowcnt As Integer = -1

        Global_strsql += " SELECT Part_no, Deliverylocation, po_no, supplier_id, labelcode, qty, duedate, stocklocation, sirlocation "
        Global_strsql += " FROM yam_con_label "
        Global_strsql += " Order by part_no "
        Global_sqlProdCN = New SqlConnection(Global_strProdCN)
        Global_sqlProdCN.Open()
        Global_sqlcmd = New SqlCommand(Global_strsql, Global_sqlProdCN)
        Global_dr = Global_sqlcmd.ExecuteReader()
        If Global_dr.HasRows Then
            While Global_dr.Read
                irowcnt += 1
                Dim srow() As String = {Global_dr.Item("Part_no"), Global_dr.Item("Deliverylocation"), Global_dr.Item("po_no"), _
                                        Global_dr.Item("supplier_id"), Global_dr.Item("labelcode"), Global_dr.Item("qty"), _
                                        Global_dr.Item("duedate"), Global_dr.Item("stocklocation"), Global_dr.Item("sirlocation")}
                dgYamahadata.Rows.Add(srow)
            End While
        End If

    End Sub

    Private Sub dgYamahadata_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgYamahadata.CellContentClick

    End Sub
End Class
0
 
tmaususerAuthor Commented:
It does not seem to matter where I place that statement. I seem to get errors one after anoter. What am I missing?
0
 
anillucky31Commented:
Have you added connection string in web.config?

once you have added connection string in web.config file then use this code.

then use this line

replace this code in original post

Global_settings = System.Configuration.ConfigurationManager.ConnectionStrings("Data Source=SERVER04N;Initial Catalog=yamahalabeldb;Integrated Security=True")


with


Global_settings = ConfigurationManager.ConnectionStrings["connectionstringname"].;

where you are getting the error.
0
 
tmaususerAuthor Commented:
I'm sorry (newbie here) but now I am really lost. I don't know what you mean by web config. I have a form open along with a datagrid in vs2010. The code I posted is what sits behind that form.

I have an app.config with the following code:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
  </configSections>
  <connectionStrings>
    <add name="Yamahadb" connectionString="Data Source=SERVER04N;Initial Catalog=yamahalabeldb;Integrated Security=True"/>
  </connectionStrings>
  <appSettings>
  </appSettings>


</configuration>
0
 
anillucky31Commented:
ohh you are working on windows form. so u will be having app.config instead of web.config.


Global_settings = System.Configuration.ConfigurationManager.ConnectionStrings("Data Source=SERVER04N;Initial Catalog=yamahalabeldb;Integrated Security=True")

with

Global_settings = ConfigurationManager.ConnectionStrings["Yamahadb"];

it will work for you
0
 
tmaususerAuthor Commented:
When I paste in that code I get the following error (see attached)..

error.doc
0
 
anillucky31Commented:
comment out you

'Global_settings = ConfigurationManager.ConnectionStrings["Yamahadb"];

and do as below

Dim Global_strProdCN As String = ConfigurationManager.ConnectionStrings["Yamahadb"];

it should work for you now
0
 
anillucky31Commented:
if that doent work try this

Dim Global_strProdCN As String = ConfigurationManager.ConnectionStrings["Yamahadb"].ConnectionString;
0
 
tmaususerAuthor Commented:
Ok. Did that and now receive an identifier expected error: Looks Like a tilda at [~"Yamahadb']; which is not shown on your line below. I can send a screen shot if needed.

  Dim Global_strProdCN As String = ConfigurationManager.ConnectionStrings["Yamahadb"];
0
 
MaxOvrdrv2Commented:
have you tried my solution?
0
 
tmaususerAuthor Commented:
Max:

Yes. Tried and got errors as well.
0
 
tmaususerAuthor Commented:
If there is a better way please post. given my lack of experience I am willing to try anything that will work at this point.
0
 
MaxOvrdrv2Commented:
try this:
'i commented the below line
'Global_settings = System.Configuration.ConfigurationManager.ConnectionStrings("Data Source=SERVER04N;Initial Catalog=yamahalabeldb;Integrated Security=True")

        Dim oCon As New SqlConnection("Data Source=SERVER04N;Initial Catalog=yamahalabeldb;Integrated Security=True")

that should solve your problem entirely.
0
 
tmaususerAuthor Commented:
receiving an error. See attached.
error1.doc
0
 
MaxOvrdrv2Commented:
2 seconds... i will re-write your original code correctly to be sure everything is correct.
0
 
MaxOvrdrv2Commented:
here's how it looks, and works, in a project i currently have on my pc:

Imports System.Data.SqlClient
Imports System.Configuration
Imports System
Imports System.IO
Imports System.Net.Mime.MediaTypeNames
Partial Class _Default







    Public Global_sqlProdCN As New SqlConnection("Data Source=SERVER04N;Initial Catalog=yamahalabeldb;Integrated Security=True")
    Public Global_ERPsettings As ConnectionStringSettings
    Public Global_strERPCN As String
    Public Global_sqlERPCN As New SqlConnection
    Public Global_dr As SqlDataReader
    Public Global_sqlcmd As New SqlCommand
    Public Global_strsql As String
    Public Global_strProdCN2 As String
    Public Global_sqlProdCN2 As New SqlConnection

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load


        Dim irowcnt As Integer = -1

        Global_strsql += " SELECT Part_no, Deliverylocation, po_no, supplier_id, labelcode, qty, duedate, stocklocation, sirlocation "
        Global_strsql += " FROM yam_con_label "
        Global_strsql += " Order by part_no "
        Global_sqlProdCN.Open()
        Global_sqlcmd = New SqlCommand(Global_strsql, Global_sqlProdCN)
        Global_dr = Global_sqlcmd.ExecuteReader()
        If Global_dr.HasRows Then
            While Global_dr.Read
                irowcnt += 1
                Dim srow() As String = {Global_dr.Item("Part_no"), Global_dr.Item("Deliverylocation"), Global_dr.Item("po_no"), _
                                        Global_dr.Item("supplier_id"), Global_dr.Item("labelcode"), Global_dr.Item("qty"), _
                                        Global_dr.Item("duedate"), Global_dr.Item("stocklocation"), Global_dr.Item("sirlocation")}
                dgYamahadata.Rows.Add(srow)
            End While
        End If

    End Sub

End Class


REMEMBER TO CHANGE THE CLASS NAME FOR YOURS (Form1 i think it was)...
0
 
tmaususerAuthor Commented:
Thank you so much for all of your assistnace. It worked perfectly.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 9
  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now